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

MySQL Database Advanced (3)-- View 1, View introduction 1, View introduction

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

2. The purpose of creating a view

Views are SQL statements for queries stored in the database, and views are created for two main reasons:

A, to achieve security. The view sets user access to the view.

Create a query that is the view of JAVA class student performance javaview, NET class student performance view netview, authorize java to access the javaview view, authorize net to access the netview view.

Create view javaviewasselect a.StudentID from TStudent a join TScore b on a.StudentID=b.StudentIDjoin TSubject c on b.subJectID=c.subJectID where a.snamememery email.subJectNamerecovera.classwriting b.mark from TStudent a join TScore b on a.StudentID=b.StudentIDjoin TSubject c on b.subJectID=c.subJectID where a.classrooms net 'create view netviewasselect

Authorize java users to access the schoolDB.javaview view

Grant select on schoolDB.javaview to 'java'@'%' identified by' 123456'

Authorize net users to access the schoolDB.netview view

Grant select on schoolDB.netview to 'net'@'%' identified by' 123456'

When using the SQL Manager client to connect to the database, java and net users can access the javaview view and the netview view, respectively.

B. Hide the complexity of data. The view can hide some data, such as the social security fund table, and can use the view to display only the name and address, but not the social security number and salary, etc. A view is like a viewport, from which you can only see some filtered columns of data.

3. The advantages of view

A. View can simplify user's operation.

The view mechanism allows users to focus on the data they care about. If the data does not come directly from the base table, you can define views to make the database look simple and clear, and simplify the user's data query operations. For example, if a view with several table joins is defined, the join operation between tables is hidden from the user. What the user makes is a simple query for a virtual table, and the user does not need to know how the virtual table is obtained.

B. View enables users to view the same data from multiple angles

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, the view provides a certain degree of logical independence for refactoring the database.

The physical independence of data means that the user's application does not depend on the physical structure of the database. The logical independence of data means that when the database is reconstructed, such as adding new relationships or adding new fields to the existing relationships, the user's application will not be affected. Hierarchical database and mesh database can generally support the physical independence of data, but can not fully support logical independence.

In relational databases, the reconstruction of databases is often inevitable. The most common way to restructure 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) is divided into studentinfo (sid,sname,sex,age) and deptinfo (sid,dept). The original table student is the result of a natural join between 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 is changed to studentinfo and deptinfo tables, the application does not need to modify, because the newly established view is defined as the user's original relationship, so that the user's external schema remains unchanged, and the user's application can still find data through the view.

Views can only provide logical independence of data to a certain extent, for example, because the update of the view is conditional, the statements in the application that modify the data may still change due to changes in the basic table construction.

D, the view can provide security protection for confidential data

When designing a database application system, different views can be defined for different users, so that confidential data does not appear on user views that should not see confidential data. For example, the student table involves the student data of 15 departments in the university, and 15 views can be defined on it. Each view contains only one department's student data, and only the dean of each department is allowed to query and modify the original department student view.

The query can be expressed more clearly by using the view properly.

For example, it is often necessary to perform a query such as "find out the course number of each student who gets the highest grade." You can first define a view to find out the highest score for each student.

4. Syntax CREATE VIEW viewname for creating views (column 1, column 2...) AS SELECT (column 1, column 2...) FROM...

Create a view of student information:

Create view studentviewas select studentID, sname, sex from TStudent; II, operation of views 1, use of views

Views are used the same as regular tables.

Select * from studentview

You cannot modify two tables at the same time on a view joined by multiple associated tables

There is an one-to-one relationship between the view and the table: if there are no other constraints (such as fields that are not in the view, which are required in the basic table), you can add, delete and modify the data.

2. Delete the view

Drop view studentview

3. Modify data through the view

If the base table of the view is a table, you can insert records into the base table through the view, requiring that columns that do not exist in the view are allowed to be empty.

A. insert data into the table through the view

Insert into studentview (studentID, sname, sex) VALUES ('01001', 'Sun WuKong', 'male')

Query inserted records, you can see that there are no columns through the view, the value is empty or the default value.

B. delete the record in the table through the view

The base table of a view can only have one table, and if there are multiple tables, you will not know which table to delete from.

Delete from studentview where studentid='01001'

C. modify the records in the table through the view

Only some columns in the view can be modified.

Update studentview set sname=' Sun WuKong 'where studentid='00001'

4. View the information of the view

View information for the view

Describe viewname;desc scoreview

View all tables and views

Show tables

View information for the view

Show fields from scoreview

5. Modify the view CREATE OR REPLACE VIEW viewname AS SELECT [...] FROM [...]; alter view studentview as select studentID as student number, sname as name, sex as gender from TStudent;6, WITH CHECK OPTION

If WITH CHECK OPTION is specified when the view is created, records that do not meet the view constraints cannot be inserted or updated when the data is updated.

3. View instance 1. Create a view using a view

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

Create view sviewas select studentID, sname, sex from studentview where studentID > 990 and sex=' male'; 2. Create a view of the student score sheet.

Create a view that contains the student number, name, subject, and grade.

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 score view, including student number, name, computer network course scores, data structure scores, and JAVA development scores.

Create view scoreviewas select studentid student number, sname name, AVG (case subjectname when 'computer network' then mark END) computer network, AVG (case subjectname when 'data structure' then mark END) data structure, AVG (case subjectname when 'JAVA development' then mark END) JAVA development from view1group by student number

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.