How to Create and Use MySQL Views
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.
ID | Name | Car Number |
1 | Buddy Baker | 28 |
2 | Dale Earnhardt Jr. | 8 |
3 | Ricky Rudd | 88 |
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.
ID | Name | Location |
1 | Talladega Superspeedway | Lincoln, AL |
2 | Daytona International Speedway | Daytona Beach, FL |
3 | Indianapolis Motor Speedway | Speedway, IN |
4 | Michigan International Speedway | Brooklyn, 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.
ID | Name | Track | Distance |
1 | Talladega 500 | 2 | 500 |
2 | Daytona 500 | 1 | 500 |
3 | Brickyard 400 | 3 | 400 |
4 | Michigan 400 | 4 | 400 |
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.
Driver | Race | Track | Location | Position |
Buddy Baker | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 3 |
Dale Earnhardt Jr. | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 2 |
Ricky Rudd | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 1 |
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.
Driver | Race | Track | Location | Position |
Buddy Baker | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 3 |
Dale Earnhardt Jr. | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 2 |
Ricky Rudd | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 1 |
Buddy Baker | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 2 |
Dale Earnhardt, Jr. | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 1 |
Ricky Rudd | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 3 |
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;
Driver | Race | Track | Location | Position |
Buddy Baker | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 3 |
Dale Earnhardt Jr. | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 2 |
Ricky Rudd | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 1 |
Buddy Baker | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 2 |
Dale Earnhardt, Jr. | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 1 |
Ricky Rudd | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 3 |
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.
Driver | Race | Track | Location | Position |
Ricky Rudd | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 1 |
Dale Earnhardt, Jr. | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 1 |
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:
Finish | Driver |
1.5 | Dale Earnhardt, Jr. |
2.0 | Ricky Rudd |
2.5 | Buddy 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.
Finish | Driver |
1.5 | Dale Earnhardt, Jr. |
2.0 | Ricky Rudd |
2.5 | Buddy Baker |
As well as:
create view race_winners as
select * from all_finishes where position = 1;
Which provides us the same results.
Driver | Race | Track | Location | Position |
Ricky Rudd | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 1 |
Dale Earnhardt, Jr. | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 1 |
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.
Driver | Race | Track | Location | Position |
Buddy Baker | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 3 |
Dale Earnhardt Jr. | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 2 |
Ricky Rudd | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 1 |
Buddy Baker | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 2 |
Dale Earnhardt Jr. | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 1 |
Ricky Rudd | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 3 |
Buddy Baker | Brickyard 400 | Indianapolis Motor Speedway | Speedway, IN | 1 |
Dale Earnhardt Jr. | Brickyard 400 | Indianapolis Motor Speedway | Speedway, IN | 3 |
Ricky Rudd | Brickyard 400 | Indianapolis Motor Speedway | Speedway, IN | 2 |
Buddy Baker | Michigan 400 | Michigan International Speedway | Brooklyn, MI | 1 |
Dale Earnhardt Jr. | Michigan 400 | Michigan International Speedway | Brooklyn, MI | 2 |
Ricky Rudd | Michigan 400 | Michigan International Speedway | Brooklyn, MI | 3 |
We can also run:
select * from race_winners;
Which gives us:
Driver | Race | Track | Location | Position |
Ricky Rudd | Daytona 500 | Daytona International Speedway | Daytona Beach, FL | 1 |
Dale Earnhardt Jr. | Talladega 500 | Talladega Superspeedway | Lincoln, AL | 1 |
Buddy Baker | Brickyard 400 | Indianapolis Motor Speedway | Speedway, IN | 1 |
Buddy Baker | Michigan 400 | Michigan International Speedway | Brooklyn, MI | 1 |
Also, this results in the series champion:
select * from standings_leader;
Which results in:
Finish | Driver |
2 | Buddy Baker |
2 | Dale Earnhardt, Jr. |
2 | Ricky 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 reusable 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.
Related Articles:
- How to Install Adminer MySQL Database Management Tool on AlmaLinux
- How to Edit the PHP Memory for Your WordPress Site via WP Toolkit
- 4 Methods for How to Install Yarn on Windows Server
- How to Install Bpytop Resource Monitoring Tool on AlmaLinux
- How to Fix “This Site Can’t Provide a Secure Connection” Error
- How to Install MongoDB on AlmaLinux

About the Author: Matt Holtz
Our Sales and Support teams are available 24 hours by phone or e-mail to assist.
Latest Articles
How to Install Adminer MySQL Database Management Tool on AlmaLinux
Read ArticleWhat is CGI-Bin and What Does it Do?
Read ArticleTop 10 Password Security Standards
Read ArticleTop 10 Password Security Standards
Read ArticleHow to Use the WP Toolkit to Secure and Update WordPress
Read Article