SQL: The Role of views; SQL views

SQL: The Role of views; SQL views

[Database] ☆★SQL: The Role of views

When complicated services are involved in the actual development process of a project, we need to use an intermediate table for data connection. Some people will say, can I use Hibernate to associate the primary and Foreign keys? Many-to-many, many-to-one, one-to-one, and so on. Using the primary and Foreign keys for association in the data operation process has a strong coupling, especially for data tables that need to be frequently deleted and modified, we do not recommend using the primary/foreign key Association mode. If we use an intermediate table, when the data is too large, the performance will be severely tested. The emergence of the SQL View, it is a good choice in solving the business logic of the intermediate table. OK. What is a view first?

1. Is the view a table? Yes, but it is a virtual table. The field of the view is customized by us. The view is for query only, and the data cannot be changed. The queried data comes from the entity table we created.

2. What are the advantages of using a view? The view can extract the required information from multiple complex associated tables to optimize the query speed.

How to Create a view?

Create three tables first:

This is a typical relationship between one-to-one and one-to-one. If the data in each table is more than 10 thousand pieces of data, what is the name of the student who is currently in the middle school?

First of all, let's analyze how to go to school at the primary school? The first is a high school, so we will have an SQL where school = 'Senior high ',

Query a List, get gradeids, and then query the studentids corresponding to so many grades based on gradeids in grade. Then, use these studentsid to query the students loop and get the name?

Is it cumbersome? Does query affect performance?

It is observed that each of the three tables is associated by id. Is it convenient to combine the three tables into one by id?

Let's associate the school table with the grade Table: We don't need this grade ID, OK?

select s.id as schoolId,s.school as schoolName,s.gradeid as gradeid,g.grade as gradeName,g.studentid as studentid from school s,grade g  where s.gradeid=g.id;

Then we associate the student table again. The student table id is the same as the studentid of the grade Table OK?

SELECT s.id as schoolId,s.school as schoolName,s.gradeid as gradeid,g.grade as gradeName,g.studentid as studentid ,t.`name` as studentName,t.age as studentAgefrom school s,grade g,student t where s.gradeid=g.id and  g.studentid=t.id;

OK, here? Let's look at the running results again?

So we want to query the names of the students attending primary school, where schoolName = 'Primary high', obtain the Object through the list loop, and use Object. getStudentName?

Therefore, you need to create the query result as a virtual table so that the operation can be performed. Use the create view name as command to create:

This means to create a virtual table named table_sgt in the query result:

create view table_sgt as(select s.id as schoolId,s.school as schoolName,s.gradeid as gradeid,g.grade as gradeName,g.studentid as studentid ,t.`name` as studentName,t.age as studentAge from school s,grade g,student t where s.gradeid=g.id and  g.studentid=t.id);

When using a view, we need to view it as a table and create an object table. The view also needs to (for example, instantiate, configure the ing file, object attribute get, set Method)

Note that the data queried by the view can only be viewed, but cannot be added, deleted, or modified!

The example database is Mysql5.5, and the tool Navicat for MySQL

OK. If you have any questions, please raise your hand?

View functions of SQL

The data shown here is actually an SQL statement equivalent to setting permissions.

For example, all employees in a department can view the payroll.
His view is select salary from salary_table;
The employee's view is select salary from salary_table where id = worker_id;

What are the advantages of using a view in SQL?

A view is a virtual table whose content is defined by the query. Like a real table, a view contains a series of columns and row data with names. However, a view does not exist in the database as a stored data value set. Rows and columns are used to define tables referenced by View queries and dynamically generate tables when views are referenced. For the referenced basic table, the view function is similar to filtering. The screening of the definition view can come from one or more tables in the current or other databases, or other view views are SQL statements stored in the database for query, mainly for two reasons: security reasons: the view can hide some data, such as the Social Insurance Fund table. You can use the view to show only the name and address, but not the social insurance number and wage number, another reason is that complex queries are easy to understand and use. View function * simplicity. What you see is what you need. A view not only simplifies users' understanding of data, but also simplifies their operations. Frequently Used queries can be defined as views, so that you do not have to specify all the conditions for each subsequent operation. * Security. Users can only query and modify the data they can see through the view. Other data in the database is neither visible nor accessible. Database authorization commands allow each user to restrict the retrieval of a database to a specific database object, but cannot authorize the database to a specific row or column. Through views, users can be restricted to different subsets of data: the permission can be restricted to the subset of rows in the base table. The permission can be restricted to a subset of columns in the base table. The permission can be restricted to the row and column subsets of the base table. The permission can be restricted to the rows restricted by the connection of multiple base tables. The permission can be restricted to the Statistical Summary of data in the base table. The permission can be restricted to a subset of another view, or a subset of some views and merged base tables. * Logical Data independence. View helps you avoid the impact of changes in the real table structure.