Views in SQL Server

a view is a table of data tables or tables with multiple data table maps, but a virtual table

The role of the view

2. Improve the security of data access

   In real-world applications, if you do not want the developer to be able to query all the records in the table, this time can create a view, to hide the important information, so that developers can not query the data.

3. Convenient query operation

   The data in the data table is often used, for example, to avoid repeated query of the SELECT Statement of this information, these times you can create a view, the frequently used data in this view, so that when using and querying the data, you can query directly through the view, without having to write complex The Select statement.

   Create a view based on single table

   This view simply extracts the data that is often used in a data table.

   Syntax format:

   CREATE viewview_name (column_name1 ...)

   As subquery

   Create a view for student information tables

   Create a view based on a multi-table connection

   Refers to a view in which data columns that are frequently used in multiple data tables are joined together by a where clause.

   
   

   (This is a view created based on a connection between the Student information table and the system table)

   Create views based on functions, grouped data

   Refers to a function that is included in a subquery statement. expressions, etc., mainly to simplify query statements, improve query efficiency

   Create VIEW V_teacher_salaryselect Dept,profession,max (Salary) as Maxsalaryfrom T_teachergroup by dept, Professionhaving Max (Salary) >3000

   
   

   (Creating views for teacher information sheets, requiring grouping of academic and faculty titles and all teachers ' wages greater than

   CHECK Constraints

   Similar to query statements, when you add a constraint (with CHECK OPTION) to a view, you conform to the criteria of the query when you execute an UPDATE statement such as INSERT, delete, and modify again.

   Create VIEW v_student asselect R.stuname,r.sex,c.deptname from T_student R, t_dept c where deptid= ' 1109024102 ' with check O Ption

   
   

   (Because a CHECK constraint is already defined in the view, the depid must be specified as 11090241032if you want to perform other operations on the image)

   Read-only view

   Similarly, if you create a read-only view, you only need to add the keyword with read at the end