In the last section, the view concept of MySQL database was introduced at the end. This chapter will give a detailed introduction to the centralized use of views. Before creating the view, we first create the student table, college table and student information table. By using the operation of the view to three tables, we can have a basic understanding and understanding of the view.

Principles for using views:

1. View name uniqueness

2. There is no limit on the number of views created. Users can create multiple views.

3. Users must get permission from the database administrator to create views.

4. Views can be nested.

5. Some database management systems prohibit users from using order by clause in query statements.

I. Creating Practical Table Data

Creating Student Table

mysql> create table studenginfo(sno int(4) zerofill,sname varchar(18),sex ENUM('male','female') not null default 'female',address varchar(48) default 'Beijing',dno int(3));
Query OK, 0 rows affected (0.07 sec)

Creating College Table

create table recruitinfo(address varchar(18) not null,score float not null,snum int(3) not null);
Query OK, 0 rows affected (0.08 sec)
mysql> show create table department\G;
*************************** 1. row ***************************
       Table: department
Create Table: CREATE TABLE `department` (
  `dno` int(2) NOT NULL,
  `dname` varchar(18) NOT NULL,
  `dnum` int(3) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Create indexes for student tables

mysql> create index name_index on studentinfo (sname);

View:

View is another form of query data. Using view, users can centralize, simplify and customize database, and provide security assurance.

Views are tables derived from one or more tables, whose structure and data are based on the query of the object. In essence, a view is a virtual table.

View creation syntax:

create view <view_name> [column1,column2...] as
select <column_name> from <tb_name>;

Where [column1,column2,...] is optional, the default is the field name in the sub-query result, and the select statement indicates the field machine data in the view.

Emphasize:

1. After the view is created, only the definition of the view is stored in the data dictionary, and the select statement is not executed.

2. Only when the user operates on the view, can the data be extracted from the basic table according to the definition of the view.

Create Views:

1. Create a view with the same information as studentinfo

mysql> create view studentinfo_view as select * from studentinfo;
select * from studentinfo_view;

2. Creating Views for Views

mysql> create view boy_view as select * from studentinfo_view where sex='male';

3. Create views for columns and view their information

mysql> create view nameaddress_view as select sname,address from studentinfo;

4. Create views with different field names from tables

mysql> create view New_view(boy_name,boy_address) as select sname,address from studentinfo where sex='male';
Query OK, 0 rows affected (0.00 sec)

5. Using Views to Simplify Complex Connections of Tables

Create an association about student information table (studentinfo), Recruitnfo and Department.

sname, dname and dnum fields.

mysql> create view join_view as select sname,dname,score from studentinfo s,department d,recruitinfo r where s.address=r.address and s.dno=d.dno;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from join_view;
+-----------+--------------------+-------+
| sname     | dname              | score |
+-----------+--------------------+-------+
| Zhang Ping      | Automobile Department             | 648.5 |
| Li Shan      | Department of Electronic Engineering         |   560 |
| Tong Wang      | Automobile Department             | 654.5 |
| Zhang Wei      | Department of Computer Engineering       |   638 |
| Gao Shou Chuan    | mechanical engineering           |   650 |
| Liu Hong      | Department of Engineering Physics         | 629.5 |
| Zhang Yong      | Department of Applied Mathematics         |   625 |
| Liu Xiao      | Department of Electronic Engineering         |   650 |
| Wu Jun      | Department of Electronic Engineering         |   631 |
| Da Shan Zhang    | Department of Materials Engineering         |   635 |
+-----------+--------------------+-------+
10 rows in set (0.03 sec)

6. Views simplify the process of complex queries

6.1 Give the student table and create boys_view view view, which contains all male classmates'information.

mysql> create view boys_view as select * from studentinfo where sex='male';

Give the recruitinfo table and create the view score_view, which contains information about all students whose admission score is higher than 630.

mysql> create view boyscore_view as select * from score_view where sno in (select sno from boys_view)；
mysql> select * from boyscore_view;
+------+-----------+-----+---------+------+
| sno  | sname     | sex | address | dno  |
+------+-----------+-----+---------+------+
| 0005 | Gao Shou Chuan    | male  | Shandong    |    3 |
| 0004 | Zhang Wei      | male  | Zhejiang    |    1 |
| 0009 | Wu Jun      | male  | Shanxi    |    4 |
| 0010 | Da Shan Zhang    | male  | Shaanxi    |    7 |
+------+-----------+-----+---------+------+
4 rows in set (0.00 sec)
mysql> create view result_view (sname,dname) as select boyscore_view.sname,department.dname from boyscore_view,department where boyscore_view.dno=department.dno;
Query OK, 0 rows affected (0.00 sec)

Delete view

Grammar: drop view_name

drop view studentinfo_view

Note: Views do not exist physically. They are just query results. They are stored queries. The create view statement only saves the definition of the view, so when dropview statement is used to delete the view, the deletion is only the definition of the view, which has no effect on the data in the actual table.