View of "Machine room refactoring" sql

Recently in the restructuring of the computer room charge system, the more you will feel here a lot of other is on the previous learning Knowledge (database, design mode) of an application and recall. For example, in the login function used in the abstract and reflection, in the students down the machine, we can use a trigger to update two tables at the same time. Here first talk about the use of the view, about the view of a bit and the role of Baidu has a lot of answers, here no longer repeat.

View definition:

understand that you can use views when it comes to multiple table operations. This prevents direct contact with the database. And when you update the database data, you will proactively update the data in the view to facilitate future queries.

Baidu Encyclopedia : A view in a computer database is a virtual table whose contents are defined by a query. As with a real table, a view includes a series of column and row data with names. However, the view does not exist in the database as a stored set of data values. Row and column data is derived from the table referenced by the query that defines the view, and is generated dynamically when the view is referenced.

View creation:

Here we create by using the Enterprise Manager and T-SQL statements in SQL Sever.

The first type: 1. Open SQL Sever will have an interface such as the following:

2. Right-click on "View" and select "New View", for example to see:

3. Select the multiple tables you want to use here, at least two tables. After selecting "Join", you will see the following:

Tick the field you want to use, and save it, the view is created successfully.

Note: The primary foreign key setting here should be noted. There is no primary foreign key when querying the data, the query result card number and the number of incorrect study numbers should be the case.

Another: Create a view by writing a statement

<span style= "FONT-FAMILY:SIMSUN;FONT-SIZE:18PX;" >create VIEW [dbo]. [V_studentinfo] Asselect     dbo. T_cardinfo.cardno, dbo. T_cardinfo.cash, dbo. T_studentinfo.studentno, dbo. T_studentinfo.studentname, dbo. T_studentinfo.classno, dbo. T_studentinfo.grade,                       dbo. T_studentinfo.department, dbo. T_studentinfo.sex, dbo. T_studentinfo.cardno as Expr1from         dbo. T_cardinfo INNER JOIN                      dbo. T_studentinfo on dbo. T_cardinfo.cardno = dbo. T_studentinfo.cardno</span>

View Application:

( Example of student information (t_studentinfo table) and Remaining Amount (T_cardinfo table ) on-machine inquiry    

First Look at the process of students on the machine: when the card number is entered, the first to inquire whether the card exists. Then, if present, the student information and the remaining amount will be displayed. This involves querying operations on two tables.

without view: When writing a query, we have to write the method of querying student information and the method of checking the remaining amount of the card.

When using a view: turning two tables into a view, you simply need to write a query method to get all the information you need to query.

use an instance of the view to show:

<span style= "font-size:18px;"    > ' d-level ' <summary> ' students on Machine ' </summary> ' ' <param name= ' Cardno ' > Search by card number </param> ' <returns></returns> ' <remarks></remarks> public Function querycardnodal (ByVal card No as Entity.v_studententity) as List (of entity.v_studententity) Implements Idal. Iline.querycardnodal Dim Helper As New SqlHelper Dim enline As New Entity.v_studententity Dim dt As D atatable Dim sql as String = "SELECT * from<strong> v_studentinfo </strong>where [email protected] "Dim params as SqlParameter () = {New SqlParameter (" @CardNo ", Cardno)} dt = Helper.querydata (sql, Commandtyp E.text, params) Dim MyList as List (of entity.v_studententity) MyList = Entity.EntityHelper.convertToList (of entity.v_studententity) (DT) Return mylist End function</span> 

Remember in the Learning database phase, Miss Rice found a few people meeting to talk about the current study. That will also tell the teacher, watching the video for views, triggers, stored procedures as if there is only a noun concept. I don't know what to do with it in detail. Now that you've used these, you'll find that it's not hard to do just that.

        

View of "Machine room refactoring" sql