Hey everybody, today I'm going to be explaining views. Views, they're virtual tables, they're not real. They're made up of fields and columns from one or more real tables, and they can be interacted with as if they were a real table. So here's an example. I'm going to select all from my employees table.
Our boss, Mr. Krabs, he would like us to create an employee attendance sheet made up of just the first and last names of all the employees. Well, I could create a view that is made from the first and last name columns of the employees table. Now, a view isn't a real table, but it can behave as if it were. I may want to create a view of the employee first and last names instead of another table because we try not to repeat data if we don't have to.
If I had two tables, one of employees and another of employee attendance, since... If I need to remove an employee, I would need to do so in two places. With the view, it's always up to date. Any changes to one or more of these real tables will also update the view.
So let's create a view of the first and last name of our employee's table. To create a view, you would type create view, then the name of the view. Let's say employee attendance, then as. I'm going to zoom in a little bit.
what would we like to select? Let's select first name, last name, from a real table. Let's say from employees.
So everything was successful. Let's refresh our schemas window. Underneath the Views category, we have a new view, Employee Attendance.
I'm going to select all from that view name, Employee Attendance. And let's see what we got. Yeah, here's our attendance sheet.
We have the first and last name columns from the employees table. This view can be interacted with as if it were a real table. I will add order by last name ascending.
You can use keywords and operators that we've learned about in previous lessons on a view. To drop a view, you would type drop view the name of the view. Employee attendance. And it's gone. All right, here's another example.
So views, they're always up to date, right? Let's create a view of customer emails. Select all from customers. Let's take a look to see what we have so far.
I'm going to add one more column, a column of customer emails. alter table customers add column email data type will be varchar I think varchar 50 should be enough space and there's our new column let's update these rows update customers set email equal to the first email will be for Fred Fish ffish at gmail.com then we'll need a where clause where customer ID equals 1 That's our first email. Customer ID 2 will be llobster at gmail.com.
3 is bbass at gmail.com. 4 will be ppuff at gmail.com. Everybody really likes Gmail, I guess. Let's create a view from these customer emails. Create view.
customer emails as, what are we selecting? Let's select the email column from our customers table. Let's refresh our schemas window. Yeah, we have a customer emails view.
Then I will select all from customer emails. And look at that! We have a listing of all the customer emails.
And we're going to spam these people with coupons or something? I don't know. When you update one or more of the real tables in your database, those views would reflect any changes. Let's add one more customer.
So let's take a look at our customers table. Select all from customers. Let's add one more customer.
Insert into our customers table. Some values. We have five columns, a customer ID, first name.
First name will be Pearl. Last name is Krabs. Referral ID, let's say null. Email will be pkrabs at gmail.com.
Okay, there's our new customer. Let's take a look at our view. Let's see if it updated. Select all from customer emails. Yeah, look at that.
It's up to date. That is one of the benefits of a view. It will update automatically because we're using components from real tables.
Any views that use that data will also be updated. The other benefit of a view is that we don't have to repeat data in a real table. Ideally, we'd only want to make a change in one place rather than across multiple tables. All right, that's a view, everybody. A view is a virtual table based on the results of an SQL statement.
They're not real tables, but they can be interacted with as if they were real. The fields found within a view are fields from one or more real tables in the database. So yeah, those are views in MySQL.