Tutorial: SQL entry exercise student achievement 5. Use view to complete multi-Table query

View concept:

• A view object is a virtual table. The result set contained in a virtual table is a query of one or more tables.
• The view itself does not save this result set, but stores the definition of the query.
• A view is a query of an existing table.
• Using a view is similar to using a table. You can use a view as a table for query. SQL Server dynamically executes the view definition to obtain the result set.

Differences between views and tables:

• View can organize data for users in a centralized manner

The columns contained in database tables are not necessarily the data required by users. Some columns are useless for multiple users, and some columns required by users are scattered in multiple different tables. You can use a view to organize the columns you need.

• A view can hide the complexity of a database.

If there is no view object, you need to understand the Entity Relationship Model of the database, understand the design of each table, and so on, and then obtain the required data through complex query statements. After creating a view, you no longer need to understand the table design and the relationship between the tables. You only need to use the simple statement "select * from view name" to obtain the required data.

• Simplify the management of User Permissions

When some columns in the source table are sensitive data, such as ID cards and credit card numbers, the administrator needs to set permissions for the columns, which is not very convenient for permission management. With the view, you can place the columns with permissions in the view, and then authorize the view object in a unified manner.

• For applicationsProgramOrganize and output data

You can query both tables and views in an application. However, querying a table may have the following potential problems:

Not conducive to development: For application developers, they need to have a detailed understanding of the design of all tables in the database. This is a relatively high requirement for application developers, and it is also a result of productivity improvement.

Unfavorable maintenance: When the database design changes, the public program also needs to be re-written, which is not conducive to the later maintenance of the program. Using views to organize data can avoid these two problems.

Create a single table View:

Create view student table view as select * from student where sex = 'male'

Insert data using views
Insert into student table view (name, sex, age, diqu) values ('smelly Kid', 'male', 23, 'taiyuan ')

 

Modify data using views

Update student table view set age = 21 where name = 'smelly kid'

 

Use views to delete data

Delete from student table view where name = 'smelly kid'

 

The scripting language for creating the views of the four tables in this database is:

View creation script
Select DBO. teacherinfo. teachername, DBO. Score. Kemu, DBO. Student. Number   As Expr1, DBO. Student. Name As Expr2, DBO. Student. Sex As Expr3,
DBO. Student. Age As Expr4, DBO. Student. * , DBO. chengji. Score, DBO. teacherinfo. teacherid, DBO. Score. scorenumber
From DBO. chengji Inner   Join
DBO. Score On DBO. chengji. scorenumber = DBO. Score. scorenumber Inner   Join
DBO. Student On DBO. chengji. Number   = DBO. Student. Number   Inner   Join
DBO. teacherinfo On DBO. Score. teacherid = DBO. teacherinfo. teacherid

Because there are multiple primary and foreign key relationships in the four tables, all data in the source table cannot be inserted or deleted using the view. We use the view to complete part of Section 3rd query scripts.

-- 1. query the scores of all students in Dalian.
Select score from alltable where diqu = 'dalian'
-- 2. query the information of students with examination scores.
Select name, age, sex, average score = AVG (score) from alltable group by name, age, sex
-- 3. Add a score of 10 to the course number 3 of all students in Dalian.
Update alltable set score = score + 3 where scorenumber = 3