MySQL 8.0.22 | Create and Select View

View — A virtual table based on the tables that already exist.

Hi guys! welcome to another MySQL session with me. Today, it’s View.

[View]

This is a virtual ‘Table’, so when you make it we use create statement, and when you delete it we use drop just like other tables. After making ‘view’ you can revise it, but it has a risk to cause error. So usually we use it only with select statement. The basic query of the ‘view’ is

create view view_name
as select select_list
from table_name where~

So basically, if you use certain select statement often on the tables, you can make the select into a virtual table ‘View’.

[Practice]

*****table1*****
create table member(
no int primary key auto_increment,
name varchar(5) not null,
addr varchar(2),
gender varchar(1) default 'w',
grade int default 1,
point int default 100);
insert into member values(default,'Nina','NY','w',2,300);
insert into member values(default,'Tom','NY','m',1,200);
insert into member values(default,'Amy','LA','w',1,150);
insert into member values(default,'Jake','DC','m',2,100);
insert into member values(default,'Lisa','LA','w',2,300);
insert into member values(default,'Nicky','NY','w',3,400);
insert into member values(default,'Tim','LA','m',4,300);
*****table2*****
create table mycat(
catno int primary key auto_increment,
userno int,
catName varchar(5),
catage int,
foreign key (userno) references member(no));
insert into mycat values (default,1,'kiki',2);
insert into mycat values (default,1,'lulu',2);
insert into mycat values (default,2,'tina',1);
insert into mycat values (default,3,'cici',1);
insert into mycat values (default,4,'toto',3);
insert into mycat values (default,4,'mio',1);

Ok, here I made these two tables. First one is the member, and the second one is their cats list.

Q. Create a view that shows members’ name and their cats’ name but only when the cat’s age is 1 year old.

Okay, this is a simple Join question. If you’re not familiar with the Join checkout my post below!

MySQL | Select Inner Join + Basic Exercise Questions(Table Nickname)

First we should select the right tuples.

mysql> select m.name, c.catName
    -> from member m, mycat c
    -> where m.no=c.userno and catage=1;
+------+---------+
| name | catName |
+------+---------+
| Tom  | tina    |
| Amy  | cici    |
| Jake | mio     |
+------+---------+
3 rows in set (0.01 sec)

And then we’re making this select statement into a virtual table ‘view’.

mysql> create view catone
    -> as select m.name, c.catName
    -> from member m, mycat c
    -> where m.no=c.userno and catage=1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from catone;
+------+---------+
| name | catName |
+------+---------+
| Tom  | tina    |
| Amy  | cici    |
| Jake | mio     |
+------+---------+
3 rows in set (0.01 sec)

Just like that, you can checkout what’s in the view with the same way to see the tuples in tables. And when you don’t need it anymore you can simply drop it.

mysql> drop view catone;
Query OK, 0 rows affected (0.01 sec)

That’s it for the view! From tomorrow, I’m gonna start to post the Java. So stay tuned! Thanks!