How to Create and Use MySQL Views

Reading Time: 6 minutes

What is a MySQL View?

A MySQL view is simply an ordinary database object that can save SQL query writers a lot of time when used correctly. A view is a stored query that a user can reference just like a table. Many times users will find themselves using the same base query over and over to solve multiple problems. Views are a way of quickly saving that query and referencing it later.  

What are the Advantages of Using Views?

Views have several advantages. First, views appear to the MySQL user just like a table. The SELECT clause can reference a view precisely like it would a table. Another advantage is that when the underlying tables referenced by a view change, the view’s results also change. A third advantage is that a view takes up very little space on the server. The view’s SQL results are calculated every time it is accessed, so they are not stored on the server until they are accessed.

Tables for this Exercise

For this article, a database will be created containing information about a fictional car racing season featuring three drivers, four tracks, and one race on each track. In this database, there are four tables.

  • Drivers
  • Tracks
  • Races
  • Finishes    

The structure for the tables is as outlined below.


create table drivers
(
  id int auto_increment,
  name varchar(64) not null,
  car_number int not null,
  constraint drivers_pk
     primary key (id)
);
create table tracks
(
  id int auto_increment,
  name varchar(64) not null,
  location varchar(64) not null,
  constraint track_pk
     primary key (id)
);
create table races
(
  id int auto_increment,
  name varchar(64) not null,
  track int not null,
  distance int not null,
  constraint races_pk
     primary key (id)
);


create table finishes
(
  id int auto_increment,
  driver int not null,
  race int not null,
  position int not null,
  constraint finishes_pk
     primary key (id)
);

Drivers

Now, in our next step, we will insert three drivers and their car numbers into a table.

  • Buddy Baker 28
  • Dale Earnhardt Jr. 8
  • Ricky Rudd 88
insert into drivers (name,car_number) values
  ('Buddy Baker',28),
  ('Dale Earnhardt Jr.',8),
  ('Ricky Rudd',88);

Our results will now show the following output.

IDNameCar Number
1Buddy Baker28
2Dale Earnhardt Jr.8
3Ricky Rudd88

Racetracks

Next, we add four racetracks and their location.

  • Talladega Superspeedway – Lincoln, AL
  • Daytona International Speedway – Daytona Beach, FL
  • Indianapolis Motor Speedway – Speedway, IN
  • Michigan International Speedway – Brooklyn, MI
insert into tracks (name,location) values
  ('Talladega Superspeedway','Lincoln, AL'),
  ('Daytona International Speedway','Daytona Beach, FL'),
  ('Indianapolis Motor Speedway','Speedway, IN'),
  ('Michigan International Speedway','Brooklyn, MI');

Our results will now show the following output.

IDNameLocation
1Talladega SuperspeedwayLincoln, AL
2Daytona International SpeedwayDaytona Beach, FL
3Indianapolis Motor SpeedwaySpeedway, IN
4Michigan International SpeedwayBrooklyn, MI

Races

Now, four races, along with the distance for each, are entered.

  • Daytona 500 ,2,500
  • Talladega 500,1,500
  • Brickyard 400,3, 400
  • Michigan 400 ,4, 400
insert into races (name,track,distance) values
  ('Daytona 500',2,500),
  ('Talladega 500',1,500),
  ('Brickyard 400',3,400),
  ('Michigan 400',4,'400');

Our results will now show the following output.

IDNameTrackDistance
1Talladega 5002500
2Daytona 5001500
3Brickyard 4003400
4Michigan 4004400

Results

Finally, the tables for the beginning of the season are now created.

In the first race at the Daytona 500:

  • Ricky Rudd finishes first
  • Dale Earnhardt Jr finishes second 
  • Buddy Baker finishes third. 
insert into finishes (driver, race, position) values
  (1,1,3),
  (2,1,2),
  (3,1,1);

Compiled Data

First Data Input

Now there is actual data to query. If we run a query to look at all driver results with their corresponding tracks and races, it is a bit complicated as there are several joins. 

select d.name as driver,
     r.name as race,
     t.name as track,
     t.location as location,
     f.position as position
  from finishes f
  left join races r
     on f.race = r.id
  left join tracks t
     on r.track = t.id
  left join drivers d
     on d.id = f.driver;

Our results now show the following output.

DriverRaceTrackLocationPosition
Buddy BakerDaytona 500Daytona International SpeedwayDaytona Beach, FL3
Dale Earnhardt Jr.Daytona 500Daytona International SpeedwayDaytona Beach, FL2
Ricky RuddDaytona 500Daytona International SpeedwayDaytona Beach, FL1

Second Data Input

As the second race, the Talladega 500, data is added, the query could be written again, the same as the earlier query. In this race, Dale Earnhardt comes in first, and Buddy Baker comes in second.

insert into finishes (driver, race, position) values
  (1,2,2),
  (2,2,1),
  (3,2,3);

The results of the above query would look like this.

DriverRaceTrackLocationPosition
Buddy BakerDaytona 500Daytona International SpeedwayDaytona Beach, FL3
Dale Earnhardt Jr.Daytona 500Daytona International SpeedwayDaytona Beach, FL2
Ricky RuddDaytona 500Daytona International SpeedwayDaytona Beach, FL1
Buddy BakerTalladega 500Talladega SuperspeedwayLincoln, AL2
Dale Earnhardt, Jr.Talladega 500Talladega SuperspeedwayLincoln, AL1
Ricky RuddTalladega 500Talladega SuperspeedwayLincoln, AL3

Create View

Now that there are multiple races in the database, there are new ways of looking at the results.  Queries can be written for “Best Finish” and “Most Wins.” These queries would all begin with the same underlying data of what drivers finished in each race’s position. To simplify the process of developing these queries, a view can be created with the “create or replace view as” a clause. This clause is followed by the SQL that is to be saved. In this case, it is added before the previous query that we showed above.

create or replace view all_finishes as
select d.name as driver,
     r.name as race,
     t.name as track,
     t.location as location,
     f.position as position
  from finishes f
  left join races r
     on f.race = r.id
  left join tracks t
     on r.track = t.id
  left join drivers d
     on d.id = f.driver;

The result is a powerful feature of SQL. This result is now something that looks like a table but will change when new information is added to any underlying table. Let’s run this query.

select * from all_finishes;
DriverRaceTrackLocationPosition
Buddy BakerDaytona 500Daytona International SpeedwayDaytona Beach, FL3
Dale Earnhardt Jr.Daytona 500Daytona International SpeedwayDaytona Beach, FL2
Ricky RuddDaytona 500Daytona International SpeedwayDaytona Beach, FL1
Buddy BakerTalladega 500Talladega SuperspeedwayLincoln, AL2
Dale Earnhardt, Jr.Talladega 500Talladega SuperspeedwayLincoln, AL1
Ricky RuddTalladega 500Talladega SuperspeedwayLincoln, AL3

The results are identical to the last time we ran this query:

The difference is that now the view can be queried like a table. A query that shows the winners of each race.

select * from all_finishes where position = 1;

The query provides these results.

DriverRaceTrackLocationPosition
Ricky RuddDaytona 500Daytona International SpeedwayDaytona Beach, FL1
Dale Earnhardt, Jr.Talladega 500Talladega SuperspeedwayLincoln, AL1

A query can also be written to display the leader based on average finish.

select avg(position) as finish,
  driver
from all_finishes
  group by driver
     order by finish;

Which shows Dale Earnhardt in the lead so far for the season:

FinishDriver
1.5Dale Earnhardt, Jr.
2.0Ricky Rudd
2.5Buddy Baker

Because views can reference other views, more extensive views can be created with these queries.

create view standings_leader as
select avg(position) as finish,
  driver
from all_finishes
  group by driver
     order by finish;

The results of the above query would look like this.

FinishDriver
1.5Dale Earnhardt, Jr.
2.0Ricky Rudd
2.5Buddy Baker

As well as:

create view race_winners as
select * from all_finishes where position = 1;

Which provides us the same results.

DriverRaceTrackLocationPosition
Ricky RuddDaytona 500Daytona International SpeedwayDaytona Beach, FL1
Dale Earnhardt, Jr.Talladega 500Talladega SuperspeedwayLincoln, AL1

Adding Data

The most potent part of views is that when we add more data like these race finishes where Buddy Baker wins the Brickyard 400 and the Michigan 400, we can just query our views to get the same results. 

insert into finishes (driver, race, position) values
  (1,3,1),
  (2,3,3),
  (3,3,2);

insert into finishes (driver, race, position) values
  (1,4,1),
  (2,4,2),
  (3,4,3);

Now we can see all finishes with this query.

select * from all_finishes;\

The results of the above query would look like this.

DriverRaceTrackLocationPosition
Buddy BakerDaytona 500Daytona International SpeedwayDaytona Beach, FL3
Dale Earnhardt Jr.Daytona 500Daytona International SpeedwayDaytona Beach, FL2
Ricky RuddDaytona 500Daytona International SpeedwayDaytona Beach, FL1
Buddy BakerTalladega 500Talladega SuperspeedwayLincoln, AL2
Dale Earnhardt Jr.Talladega 500Talladega SuperspeedwayLincoln, AL1
Ricky RuddTalladega 500Talladega SuperspeedwayLincoln, AL3
Buddy BakerBrickyard 400Indianapolis Motor SpeedwaySpeedway, IN1
Dale Earnhardt Jr.Brickyard 400Indianapolis Motor SpeedwaySpeedway, IN3
Ricky RuddBrickyard 400Indianapolis Motor SpeedwaySpeedway, IN2
Buddy BakerMichigan 400Michigan International SpeedwayBrooklyn, MI1
Dale Earnhardt Jr.Michigan 400Michigan International SpeedwayBrooklyn, MI2
Ricky RuddMichigan 400Michigan International SpeedwayBrooklyn, MI3

We can also run:

select * from race_winners;

Which gives us:

DriverRaceTrackLocationPosition
Ricky RuddDaytona 500Daytona International SpeedwayDaytona Beach, FL1
Dale Earnhardt Jr.Talladega 500Talladega SuperspeedwayLincoln, AL1
Buddy BakerBrickyard 400Indianapolis Motor SpeedwaySpeedway, IN1
Buddy BakerMichigan 400Michigan International SpeedwayBrooklyn, MI1

Also, this results in the series champion:

select * from standings_leader;

Which results in:

FinishDriver
2Buddy Baker
2Dale Earnhardt, Jr.
2Ricky Rudd

Conclusion

When we save a query in our database server (more specifically in the database catalog) and give it a name, this newly named query is called a database view, or, more simply, a view. MySQL Views are a powerful way to save vital and reuseable queries that can help us speed the retrieval of important information. Because these save views can reference other views, more detailed views can be created with these substantial queries.

Rate Us!

We pride ourselves on being The Most Helpful Humans In Hosting™!

Our Support Teams are filled with experienced Linux technicians and talented system administrators who have intimate knowledge of multiple web hosting technologies, especially those discussed in this article.

Should you have any questions regarding this information, we are always available to answer any inquiries with issues related to this article, 24 hours a day, 7 days a week 365 days a year.

If you are a Fully Managed VPS server, Cloud Dedicated, VMWare Private Cloud, Private Parent server, Managed Cloud Servers, or a Dedicated server owner and you are uncomfortable with performing any of the steps outlined, we can be reached via phone @800.580.4985, a chat or support ticket to assisting you with this process.

Was this article helpful? Let us know!

What is Configuration Management?

Reading Time: 4 minutes
Config.mgmt3

Configuration management is the process by which a company or organization defines and tracks the state of its infrastructural resources. Encapsulated in those resources are both physical hardware and software. It is a means to ensure that when changes are made to a system, those changes are tracked, geared toward the ultimate predefined criteria of what state should be.

Continue reading “What is Configuration Management?”

How to Convert .htaccess Rules to NGINX Directives

Reading Time: 6 minutes

NGINX is a web server that is becoming an increasingly popular option for web hosting, as sixteen percent of all sites on the internet are utilizing NGINX. This percentage is constantly increasing as clients require a web server that can serve content faster. It can also be used for proxies, reverse proxies, load balancing, and more depending on what modules you load onto NGINX. One of the significant differences between Apache (a popular webserver) and NGINX is the way each system handles access rules. If you are familiar with using .htaccess rules in Apache, then the method that NGINX uses of including directives in the server’s vhost block will be a substantial change.

Continue reading “How to Convert .htaccess Rules to NGINX Directives”

Locations of Common Log Files on cPanel Servers

Reading Time: 2 minutes

One of the nice things about cPanel-based servers is the way that they keep the location of key files in the same place across all the various cPanel versions.  Due to this consistency one always knows where to look for logs files for all services running on a cPanel server.

Continue reading “Locations of Common Log Files on cPanel Servers”

How to Install and Configure SmartCTL

Reading Time: 5 minutes

What is SmartCTL?

Every modern hard drive has an option to monitor its current status and health via SMART attributes. SMART stands for Self-Monitoring, Analysis, and Reporting Technology. The SMART test can be performed on your HDD to detect any potential problems with the hardware itself. Tests such as these are run using SmartCTL. According to the Linux man page, SmartCTL is a command-line utility designed to perform SMART tasks. Examples of these tasks would be printing error logs or enabling and disabling automatic SMART testing. 

So without further ado, let’s dig into the SmartCTL command. 

Continue reading “How to Install and Configure SmartCTL”

How to Install Kubernetes on Ubuntu, macOS, and Windows

Reading Time: 6 minutes

What is Kubernetes?

Kubernetes (or K8s) is an open-source container orchestration system for automating computer application deployment, scaling, and management. Kubernetes manages and runs Docker containers on numerous hosts. The project was started by Google and is supported by many companies, including Microsoft, RedHat, IBM.

Continue reading “How to Install Kubernetes on Ubuntu, macOS, and Windows”

How to Install and Configure K3s on Ubuntu 18.04

Reading Time: 7 minutes

What is K3s?

k3s Logo

K3s is a lightweight version of Kubernetes. It is a highly available Kubernetes certified distribution designed for production workloads in unattended, limited resource, remote locations, or inside an IoT appliance. The developers of K3s declare that K3s is capable of almost everything that K8s can do. 

Continue reading “How to Install and Configure K3s on Ubuntu 18.04”

What is Puppet and What Role Does it Play in DevOps?

Reading Time: 5 minutes

What is Puppet?

puppet-labs-logo

Puppet is a cross-platform client-server based application used for configuration management. It handles the software and its configurations on multiple servers. There are two versions available. One is open-source, the other is a commercial version. It works on both Linux and Windows platforms. It uses a declarative approach to automate updates, installations, and other tasks. This feature allows the software to configure those systems using files called manifests. A manifest contains the instructions for a group or type of server(s) being controlled. 

Continue reading “What is Puppet and What Role Does it Play in DevOps?”

What is a Virtual Private Network and How Does it Work? 

Reading Time: 6 minutes

What is a VPN?

vpn1

A Virtual private network (VPNs) creates a secure connection between a remote computer and another network. The connection itself is encapsulated and encrypted which provides a protected communications channel between the two points.

A VPN is becoming more crucial in today’s security-related society. Due to increasing safety concerns, the proportional use of these services is growing exponentially. This technology targets several critical aspects of our online experience: mainly privacy and security. 

Continue reading “What is a Virtual Private Network and How Does it Work? “

What is NoSQL and How is it Utilized?

Reading Time: 5 minutes

What is NoSQL?

A NoSQL or a NoSQL Database is a term used when referring to a “non SQL” or “not only SQL” database. NoSQL databases store data in a different format than a traditional relational database management systems. This is why NoSQL is often associated with the term “non-relational” database. Simply put, NoSQL databases are modern databases with high flexibility, blazing performance, and built for scalability. These databases are used when you require low latency and high extensibility while working with large data structures. The versatility of NoSQL is due to the nature of as being unrestricted in comparison to relational databases models such as MySQL or DB2. 

Continue reading “What is NoSQL and How is it Utilized?”