MySQL database Advanced (iii)--view

MySQL database Advanced (iii)--view One, view introduction 1, Introduction to the View

A view is a virtual table defined by a SELECT query statement and is a very efficient way to view the data. A view contains a series of data columns and rows with names, but the data in the view does not really exist in the database, and the view returns the result set.

2. Purpose of creating a view

Views are SQL statements that are stored in queries in the database, and the view is created primarily for two reasons:
A, to achieve security. View to set the user access rights to views.
Creating a query is the view of the Java class Student score Javaview, NET class student scores NetView, authorized Zhang to access Javaview view, authorized Wangk to access NetView view.

create view javaviewasselect a.StudentID,a.sname,email,c.subJectName,a.class,b.mark from TStudent a join TScore b on a.StudentID=b.StudentIDjoin TSubject c on b.subJectID=c.subJectID where a.class=‘JAVA‘;create view netviewasselect a.StudentID,a.sname,email,c.subJectName,a.class,b.mark  from TStudent a join TScore b on a.StudentID=b.StudentIDjoin TSubject c on b.subJectID=c.subJectID where a.class=‘NET‘;

Authorizing Java users to access the Schooldb.javaview view
grant select on schoolDB.javaview to ‘java‘@‘%‘ identified by ‘123456‘;
Authorized NET user Access Schooldb.netview view
grant select on schoolDB.netview to ‘net‘@‘%‘ identified by ‘123456‘;
When you use the SQL Manager client to connect to a database, Java, net users can access the Javaview view and the NetView view, respectively.
B, hidden data complexity. Views can hide some data, such as: Social insurance fund tables, you can use the view to display only names, addresses, and do not show social security numbers and wages, etc. A view is like a viewport, and only some of the filtered data columns are visible from the viewport.

3. Advantages of the View

A, the view can simplify the user operation
The view mechanism allows users to focus on the data they care about. If the data is not directly from the base table, you can define the view to make the database look simple, clear, and simplify the user's data query operations. For example, a view that defines a number of table connections hides the connection operation between tables and tables from the user. What the user does is a simple query of a virtual table, and how the virtual table is derived, and the user does not need to know.
B, views enable users to view the same data in multiple ways
The view mechanism enables different users to view the same data in different ways when many different kinds of users share the same database.
C, view provides a degree of logical independence for refactoring database
The physical independence of data means that the user's application is not dependent on the physical structure of the database. The logical independence of data is that when a database is re-constructed, such as adding new relationships or adding new fields to existing relationships, the user's application is not affected. Hierarchical database and mesh database generally can support the physical independence of the data, but the logical independence can not be fully supported.
In a relational database, the reconfiguration of a database is often unavoidable. The most common way to refactor a database is to divide a base table "vertically" into multiple base tables. For example: The student relationship student (Sid,sname,sex,age,dept,leader), divided into Studentinfo (sid,sname,sex,age) and Deptinfo (sid,dept) two relationships. The original table student is the result of a natural connection to the Studentinfo table and the Deptinfo table. If you create a view student:

CREATE VIEW student(sid,sname,sex,age,dept) AS SELECT studentinfo.sid,studentinfo.sname,studentinfo.sex,studentinfo.age,deptinfo.dept FROM studentinfo, deptinfo WHERE studentinfo.sid=deptinfo.sid;

Although the logical structure of the database becomes studentinfo and deptinfo two tables, the application does not have to be modified because the newly created view is defined as the user's original relationship, leaving the user's external mode intact, and the user's application can still find the data through the view.
Views can only provide a certain degree of logical independence of the data, for example, because the update of the view is conditional, the statement that modifies the data in the application may still change due to changes in the base table construction.
D, view can provide security for confidential data
When you design a database application, you can define different views for different users so that confidential data is not present on a user view that should not see confidential data. For example, the student table covers 15 faculties of the school student data, on which 15 views can be defined, each of which contains only one faculty student data, and only allows each faculty director to query and modify the primitive student view.
E, the appropriate use of views to more clearly express the query
For example, it is often necessary to perform such a query "to find out the course number for each student who has obtained the highest grade". You can define a view to find the highest score for each student.

4. Syntax for creating views

CREATE VIEW viewname(列1，列2...) AS SELECT (列1，列2...) FROM ...;

To create a view of student information:

create view studentviewas select studentID, sname, sex from TStudent;

Second, the operation of the view 1, the use of the view

The use of views is the same as for normal tables.
select * from studentview;
You cannot modify two tables at the same time on a view that is connected by more than one associated table.
A view is a one-to-many relationship with a table: if there are no other constraints (such as a field that is not in the view, which is required in the base table), the data manipulation can be increased.

2. Delete View

drop view studentview;

3. modifying data through views

If the base table of the view is a table, you can insert records through the view Shangki, which requires that the columns that are not in the view are allowed to be empty.
A. Inserting data into a table through a view
insert into studentview(studentID, sname, sex)VALUES(‘01001‘, ‘孙悟空‘, ‘男‘);
Query the inserted records, you can see columns that are not available through the view, and the values are empty or default values.

B. Delete records from a table through a view
The base table for a view can have only one table, and if you have multiple tables, you will not know which table to delete from.
delete from studentview where studentid=‘01001‘;
C. Modifying records in a table through a view
Only columns in the view can be modified.
update studentview set sname=‘孙悟空‘ where studentid=‘00001‘;

4. View information

To view information for a view

describe viewname;desc scoreview;

View all tables and views
show tables;
To view information for a view
show fields from scoreview;

5. Modify the View

CREATE OR REPLACE VIEW viewname AS SELECT [...] FROM [...];alter view studentview as select studentID as 学号, sname as 姓名, sex as 性别 from TStudent;

6. With CHECK OPTION

If you specify with CHECK OPTION when you create the view, you cannot insert or update records that do not conform to the view restrictions when you update the data.

Iii. View instance 1, using views to create a view

The table that creates the query for the view is called the base table, and the base table can be a view and a table.

create view sviewas select studentID, sname, sex from studentview where studentID>990 and sex=‘男‘;

2. Create a view of the Student score table

Create a view that contains student numbers, names, disciplines, and scores.

create view view1as select a.StudentID,a.Sname,c.subJectName,b.mark  from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID;

Create a performance view that includes the number, name, computer network course scores, data structure scores, Java development achievements.

create view scoreviewas select studentid 学号,sname 姓名,AVG(case subjectname when ‘计算机网络‘ then mark END) 计算机网络,AVG(case subjectname when ‘数据结构‘ then mark END) 数据结构,AVG(case subjectname when ‘JAVA开发‘ then mark END)  JAVA开发 from view1group by 学号;

MySQL database Advanced (iii)--view