7. View operation of MySQL database

The previous content introduced the view concept of the MySQL database at the end stage, which is a fine introduction to the centralized usage of the view. Create a student table, a college table, and a learner's information table before creating a view, so that we have a basic understanding and understanding of the view by using views of the three tables.

Principles for using Views:

1. Uniqueness of view name

2, the number of views created unlimited, users can create multiple views

3, the user creates the view, must obtain the authority from the database administrator.

4. Views can be nested,

5. Some database management systems prohibit users from using the ORDER BY clause in query statements.

First, create the utility table data

Create student Tables

Mysql> CREATE TABLE Studenginfo (Sno int (4) zerofill,sname varchar, sex ENUM (' male ', ' female ') not null default ' female ', address VA Rchar (+) Default ' Beijing ', DNO Int (3)); Query OK, 0 rows affected (0.07 sec)

Create a college table

CREATE TABLE Recruitinfo (address varchar) not null,score float not null,snum int (3) is not NULL); Query OK, 0 rows affected (0.08 sec) mysql> Show create TABLE department\g;*************************** 1.  Row *************************** table:departmentcreate table:create Table ' Department ' (' DNO ' int (2) not NULL, ' dname ' varchar is not NULL, ' Dnum ' int (3) is not null default ' 0 ') engine=innodb default charset=utf81 row in Set (0.00 SE C

Create an index for a student table

Mysql> CREATE index Name_index on Studentinfo (sname);

Second, view:

Views, as another form of querying data, enable users to centralize, simplify, and customize databases while providing security assurance

A view is a table that is exported from one or more tables whose structure and data are based on the underlying query. In essence, a view is a virtual table.

View creation Syntax:

Create VIEW <view_name> [Column1,column2 ...] asselect <column_name> from <tb_name>;

where [Column1,column2,...] As an option, the default is the field name in the subquery result, and the SELECT statement indicates the field machine data in the view.

Emphasize:

1. Once the view is created, only the definition of the view is stored in the data dictionary, and the SELECT statement does not perform

2. Only when the user is working on the view, the data is removed from the base table as defined by the views.

To create a view:

1. Create a view with the same information as Studentinfo

Mysql> CREATE VIEW Studentinfo_view as SELECT * from Studentinfo;select * from Studentinfo_view;

2. Create a view for the view

Mysql> CREATE VIEW Boy_view as SELECT * from Studentinfo_view where sex= ' man ';

3. Create a view for the column and view its information

Mysql> CREATE View Nameaddress_view as select sname,address from Studentinfo;

4. Create a view with a table with a different field name

mysql> CREATE View New_view (boy_name,boy_address) as select sname,address from Studentinfo where sex= ' man '; Query OK, 0 rows Affected (0.00 sec)

5. Use view to simplify the complex connection of table

Create an association of Student information Tables (STUDENTINFO), Admissions information sheets (recruitinfo), and department.

Three fields of Sname, Dname, and Dnum.

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       |  Auto Department               | 648.5 | |   Li Shan       |  Department of Electronic Engineering           |   560 | |   Wang Tong       |  Automotive Systems               | 654.5 | |   Zhang Wei       |  Department of Computer Engineering        |    638 | |   Gaoshou     |  Mechanical Engineering             |   650 | |   Liu Hong       |  Engineering Physics Department           | 629.5 | |   Zhang Yong       |  Applied Mathematics Department           |   625 | |   Lynn       |  Department of Electronic Engineering           |   650 | |   WU       |  Department of Electronic Engineering           |   631 | |   Zhangdashan     |  Materials Engineering Department          |    635 |+-----------+--------------------+-------+10 rows in set  (0.03 sec) 

6. View simplifies the process of complex queries

6.1 Give student table, create Boys_view view, include all male students information

Mysql> CREATE VIEW Boys_view as SELECT * from Studentinfo where sex= ' man ';

Give Recruitinfo table, create view Score_view, include information for all students with admission score above 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 |  Gaoshou     |  men   |  Shandong      |    3 | |  0004 |  Zhang Wei       |  men   |  Zhejiang      |    1 | |  0009 |  Wu       |  male   |  Shanxi      |    4 | |  0010 |  Zhangdashan     |  men   |  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 a view

Syntax: Drop View view_name

Drop View Studentinfo_view

Note: The view is physically nonexistent, just a query result, which is a stored query. The CREATE VIEW statement simply saves the definition of the view, so when you delete a view by using the Drop VIEW statement, the definition of the view is deleted, and there is no effect on the data in the actual table.

This article is from "Little Chong" blog, please make sure to keep this source http://maoxiaoxiong.blog.51cto.com/11705634/1983954

7. View operation of MySQL database