Shulou(Shulou.com)06/01 Report--

The following is to understand the operation of the MySQL database on the view, I believe you will benefit a lot after reading, the text is not much in the essence, I hope that the MySQL database on the operation of the view of this short content is what you want.

Principles for using views:

1. Uniqueness of view name

2. The number of views created is unlimited, and users can create multiple views.

3. The user must get permission from the database administrator to create the view.

4. Views can be nested

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

First, create practical table data

Create a 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)

Create a 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: departmentCreate 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=utf81 row in set (0.00 sec)

Create an index for the student table

Mysql > create index name_index on studentinfo (sname)

Second, the view:

View as another form of query data, using view, users can centralize, simplify and customize the database, while providing security guarantee

A view is a table that is derived from one or more tables, and its structure and data are based on the query against the object. In essence, the view is a virtual table.

View creation syntax:

Create view [column1,column2...] Asselect from

Where [column1,column2,...] Optional, the default is the field name in the results of the subquery, 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 in it is not executed

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

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 different field name from the table

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. simplify the complex join of the table by using the view

Create an association about the student information form (studentinfo), the admissions information form (Recruitinfo), and the Department.

Three fields: sname, dname and dnum.

Mysql > create view join_view as select sname,dname,score from studentinfo department djimitinfo r where s.address=r.address and s.dnostingd.dnobilitQuery 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 | 654.5 | | Wang Tong | Department of Automotive | 654.5 | | Zhang Wei | Department of computer Engineering | 638 | | Gao Shouchuan | Mechanical Engineering | 629.5 | Zhang Yong | Department of Applied Mathematics | 625 | Liu Xiao | electricity | Subengineering Department | 650 | | Wu Jun | Department of Electronic Engineering | 631 | | Zhang Dashan | Department of material Engineering | 635 | +-+ 10 rows in set (0.03 sec)

6. View simplifies the process of complex query

6.1 give the student a table and create a boys_view view containing all the male students' information

Mysql > create view boys_view as select * from studentinfo where sex=' Man'

Give the recruitinfo table and create a view score_view that contains information about all students with admission scores 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 Shouchuan | male | Shandong | 3 | 0004 | Zhang Wei | | male | Zhejiang | 1 | | 0009 | Wu Jun | male | Shanxi | 4 | 0010 | Zhang Dashan | male | Shaanxi | 7 | +-+ 4 rows in set (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

Syntax: drop view view_name

Drop view studentinfo_view

Note: the view doesn't exist physically, it's just a query result, it's a stored query. The create view statement only saves the definition of the view, so when you use the drop view statement to delete the view, it only deletes the definition of the view and has no effect on the data in the actual table.

After reading this article on how to operate views in MySQL database, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.