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!

How to Clear Browser Cache and Cookies

Reading Time: 4 minutes

What is the Browser Cache?

The browser cache is used for storing website data like HTML, CSS, JavaScript, and images. Caching is a method of storing data that can be used later. Like other programs that you may have installed, browsers use caching to store website data on your computer instead of requesting the data from a web server to make web pages load faster.

Continue reading “How to Clear Browser Cache and Cookies”

Renaming Database Tables with PhpMyAdmin

Reading Time: 2 minutes

Renaming a table within a database is a task that may need to be done occasionally. You might just need to rename it for clarification purposes, to archive old data, to make a copy when a restore is being done, or any number of other reasons. PhpMyAdmin helps you rename a database quickly with just a few clicks.

Continue reading “Renaming Database Tables with PhpMyAdmin”

Searching Through a Database with PhpMyAdmin

Reading Time: 3 minutes

Using PhpMyAdmin to search for records and information in your database can quickly help you get the information you need without having to run advanced search queries. This tutorial assumes you have already logged in to PhpMyAdmin, and shows how to search by keyword and for a range of records.

Continue reading “Searching Through a Database with PhpMyAdmin”

Running SQL Queries on a Database with PhpMyAdmin

Reading Time: 2 minutes

In this tutorial, we will explore how to run SQL queries on a database within PhpMyAdmin. SQL stands for Structured Query Language. SQL statements or queries are used to perform database tasks such as searching, updating, or retrieving data from a database.

Continue reading “Running SQL Queries on a Database with PhpMyAdmin”

What is Machine Learning?

Reading Time: 3 minutesIt was 2017 when American businessman Mark Cuban said that if you don’t understand artificial intelligence, deep learning and machine learning “you’ll be a dinosaur within three years.” Time will tell as to whether he is right, but if his theory has substance, some companies are well into the 12-month countdown of becoming extinct.

What is Machine Learning?

In its purest form, machine learning teaches computers to learn in the same way that humans do. It collects and interprets data from the world around us and makes decisions on what to do with that information. Machine learning is one of the first applications of artificial intelligence.

Just think about every time you start a search using Google. How can it find all the relevant matches to your terms? Considering there are 30 trillion unique web pages that search engines trawl to retrieve what you need, it is even more impressive. It’s impossible for a human to explore that many pages in a lifetime. This is the essence of machine learning, without intervention computers learn to use data to accomplish human tasks in a fraction of the time.

 

Machine Learning and Data

It is almost impossible to stress just how vital data is to machine learning; in fact, they are just about synonymous with each other. This is probably best summarised within the Data Science Hierarchy of Needs penned by Rogati, 2017.

At the top of the hierarchy is the AI or Deep Learning algorithm. This might be the algorithm that recommenders which Netflix show to watch or Amazon Alexa responding to your voice command. However, at the very start of the journey is data collection and the quality of what feeds the algorithm.

As an example, marketing teams use machine learning applications to hyper-personalize communications. This is why we tend to get emails or notifications that are highly relevant and tailored to our needs. The machine has studied our data and knows exactly what we need and when we want it. Had the initial data been incorrect or “dirty” in any way, customers would receive communications that are not relevant. What if somebody had accidentally entered a customer location as the U.K. on an order form instead of the U.S. and all pricing is calculated pounds instead of dollars? The customer would soon unsubscribe to an email list because it doesn’t pertain to them.

A company can have the best algorithms in the industry, but without quality data, they are effectively useless and possibly detrimental. To counter these problems, companies deploying machine learning technology will usually start by designing a data quality or governance strategy which negates the risk. Adopting AI is a journey and must begin with getting the simple things right.

 

Machine Learning Framework

Hiring a team to design and deploy machine learning applications can be costly. While Data Scientists are usually specialists in statistical methods and incredibly adept with coding languages like Python and R; they often find it hard to present findings to Data Analysts or Insight Managers. However, the algorithms also need to be deployed onto platforms requiring a Data Engineer or Developer. There also needs to be duplicate roles to avoid single points of failure, and of course, everybody needs powerful processors that can analyze vast amounts of data. Suddenly, one Data Scientist has become a team of 8 people with expensive hardware and costs have escalated!

The role of machine learning has been growing exponentially in the last few years, and it looks set to continue with recent developments in cloud, edge and quantum computing which will only increase the potential processing power. Companies who fail to realize the capability of AI will fall behind the competition.

Our Cloud Sites service is a fine example of how machine learning works in a hosting environment. This PaaS allows your websites to scale as your site grows, without having to worry about scheduling downtime to resize and upgrade your server! Our one-click install of popular CMS’s makes working on your sites that much easier.

What is ModDeflate?

Reading Time: 3 minutes

How mod_deflate works

When a visitor accesses a website, a request is made to the web server for a specific kind of data. An example might be a home page of a site. Next, the web server locates that data and delivers it to the client who is requesting that data – basically back to the web browser.

In this example, the speed at which the home page loads can depend on a variety of factors. One of them could be how long it takes to find and deliver the data for that page. This is just one example.

Some of that data – such as javascript files, css files, and php files – can actually be compressed into smaller sizes before they are delivered back to the visiting client or browser at the smaller size. The visitor can now have a more optimized browsing experience.

This is where mod_deflate comes in.

Continue reading “What is ModDeflate?”

Copying a Database Table with PhpMyAdmin

Reading Time: 2 minutes

Copying a table is quick and easy to do within PhpMyAdmin. Whether you are trying to make a backup copy before changes are made, archiving data, moving data to a new site or application, or even just working on restructuring your database, PhpMyAdmin can help you get the job done in just a few clicks.

This guide assumes that you have already logged in to PhpMyAdmin.We will go over how to copy a table and go over a few of the options PhpMyAdmin gives us.

Continue reading “Copying a Database Table with PhpMyAdmin”

How To Repair a MySQL Database in cPanel

Reading Time: < 1 minute

This tutorial assumes you’ve already logged in to cPanel, and are starting on the home screen. Let’s learn how to repair a database.

  1. Click the “MySQL Databases” icon.cpanel-pl-mysql-10-repair-02
  2. Under “Modify Databases”, and in the “Repair Database” drop down box, select the database you want to repair.cpanel-pl-mysql-10-repair-03
  3. Then click “Repair Database”.cpanel-pl-mysql-10-repair-04
  4. That’s it! The database has been repaired.cpanel-pl-mysql-10-repair-05

 

MySQL 5.1 vs. 5.5 vs. 5.6 Performance Comparison

Reading Time: < 1 minute

General Results
  • MySQL 5.5 and 5.6 both performed significantly better than MySQL 5.1.
  • Read/Write and Read Only workloads testing requests per second show a marked improvement for both MySQL 5.5 and 5.6 over MySQL 5.1.
  • The 95th percentile response times (explained below) for both Read/Write and Read Only workloads on MySQL 5.5 and 5.6 were much lower (thus faster) than MySQL 5.1.
Requests per Second

MySQL 5.1 vs. 5.5 vs. 5.6 Performance Comparison - Requests per Second - Read and Write Operations

Continue reading “MySQL 5.1 vs. 5.5 vs. 5.6 Performance Comparison”