Personal home page ： I thought about this nickname 20 minute
Previous columns ：【 A quick way 】jQuery

---

️ special column ：【 A quick way 】SQL server
Looking back ：
【SQL server A quick way 】 Database foundation
【SQL server A quick way 】 Databases and tables （ One ）
【SQL server A quick way 】 Databases and tables （ Two ）
【SQL server A quick way 】 Database query

View

   Once the view is defined , Can be used as a watch Inquire about 、 modify 、 Delete and to update . Using views has the following advantages ：

（1） Centralize data for users , Simplify user's data query and processing . Sometimes the data users need is scattered in multiple tables , Defining views brings them together , Thus, it is convenient for users to query and process data .
（2） Mask the complexity of the database . Users do not have to understand the table structure in a complex database , And the change of the database table does not affect the user's use of the database .
（3） Simplify the management of user rights . Just grant the user permission to use the view , Instead of specifying that users can only use specific columns of the table , It also increases security .
（4） Easy to share data . Each user does not have to define and store the data they need , Data that can be shared in the database , In this way, the same data only needs to be stored once .

1. Create view

  1． Create a view in object Explorer
   Next, I'll put it in xsbook Create in the database cs_xs（ Describe the situation of computer students ） A view describes the process of creating a view .

  （1） start-up “SQL Server Management Studio” stay “ In object Explorer ” an “ database ”“xsbook” Select one of “ View ” term , Right click mouse , Select... From the shortcut menu that pops up “ New view (N)” A menu item .
  （2） In the subsequent add table window , Add the basic table that needs to be associated 、 View 、 function 、 A synonym for . Only the table tab is used here , Selection table “xs”, Pictured 4.36 Shown , single click “ add to ” Button .

  （3） After adding the base table , All column information of the base table is displayed in the diagram window of the view window , Pictured 4.37 Shown .

  （4） After the last step , Click the Save button on the panel , The save View dialog box appears , Enter the view name in it cs_xs, And click “ determine ” Button , This completes the creation of the view .
The way to check is ： start-up “SQL Server Management Studio”→ stay “ In object Explorer ” an “ database ”→“xsbook”→“ View ”→ choice “dbo.cs_xs”, Right click mouse , Choose... From the shortcut menu that pops up “ Design ” A menu item , You can view and modify the view structure , choice “ Before editing 200 That's ok ” A menu item , You will be able to view the view data content .

  2． Use CREATE VIEW Statement to create a view

  T-SQL The statement used to create a view in is CREATE VIEW sentence , For example, use this statement to create a view cs_xs, It is expressed in the form of ：

CREATE VIEW cs_xs
AS 
    	SELECT *
    		FROM xs
    		WHERE  major  = ' Computer '

   Be careful ：CREATE VIEW Must be the first statement of the batch command .

  CREATE VIEW Of Grammar format by ：

CREATE VIEW [ < Database schema name >. ] < View name > [ (< Name > [ ,...n ] ) ]
	AS select_statement
	[ WITH CHECK OPTION ]

explain ：

（1）< Name >： If you use the same column name as in the source table or view , You don't have to give column_name.
（2）select_statement： Used to create views SELECT sentence , Can be found in SELECT Statement to query multiple tables or views , To indicate the table or view referenced by the newly created view .
（3）WITH CHECK OPTION： Point out that all changes made on the view should conform to select_statement Constraints specified , That is to ensure the modification of 、 The inserted and deleted rows meet the conditions in the view definition , This ensures that after data modification , The modified data can still be seen through the view .

  【 example 】 establish cs_jy View , Including the library card number of each student majoring in computer 、 The request number and borrowing time of the borrowed books . Make sure that all changes to the view meet the condition of professional computer .

CREATE VIEW cs_jy
AS 
   	SELECT xs. Library card number ,  Cable number ,  Borrowing time 
   		FROM xs, jy
   		WHERE xs. Library card number  = jy. Library card number  AND  major  = ' Computer '
   	WITH CHECK OPTION

   View cs_jy The attribute column of includes xs The library card number of the form 、jy The book request number and borrowing time of the form .

  【 example 】 Create computer majors in 2022 year 4 month 20 View of book borrowing before the day cs_jy_430.

CREATE VIEW cs_jy_430
   	AS
   	SELECT  Library card number ,  Cable number ,  Borrowing time 
   		FROM cs_jy
      	WHERE  Borrowing time <'20220420'

   The view here cs_jy_430 Is based on the view cs_jy Above .

  【 example 】 Define a view that reflects book lending .

CREATE VIEW LENDNUM(ISBN,num)
    AS
    SELECT ISBN, The number of copies - Inventory 
        FROM book

explain ：LENDNUM A view is a view with an expression , The amount lent num It's calculated .

  【 example 】 A view that defines the total value of books borrowed by students .

CREATE VIEW TOTPRICE( Library card number ,PRICE)
    AS
    SELECT jy. Library card number ,SUM( Price )
        FROM xs,jy,book
        WHERE xs. Library card number =jy.  Library card number  AND jy.ISBN=book.ISBN
        GROUP BY jy. Library card number 

2. Query view

  【 example 】 Find computer majors in 1996 year 1 month 1 Students born after the th .
This example is right cs_xs View to query ：

SELECT *
    FROM cs_xs
    WHERE  time of birth >'19960101'

  【 example 】 Find in 2022 year 3 month 11 The student's library card number and book retrieval number .
This example is right cs_jy View to query ：

SELECT  Library card number ,  Cable number 
    FROM cs_jy
    WHERE  Borrowing time ='20220311'

  【 example 】 Find in 2022 year 4 month 30 The student's library card number and Book request number who borrowed the book a few days ago .
This example is right cs_jy_430 View to query ：

SELECT  Library card number ,  Cable number 
    FROM cs_jy_430

  【 example 】 Find the number of loans in 3 Ben or 3 Of more than books ISBN And lending .
This example is right LENDNUM View to query ：

SELECT * 
    FROM LENDNUM
    WHERE num>=3

  【 example 】 Find the value of borrowed books in 100 The library card number and the value of the books borrowed by students above yuan .
This example is right TOTPRICE View to query ：

SELECT * 
    FROM TOTPRICE
    WHERE PRICE>100

   When using view queries , If a new field is added to its associated basic table , You must recreate the view to query the new field . for example , if xs Table added “ Native place ” Field , So the view created on it cs_xs If you don't rebuild the view , Then the following query ：

SELECT *  FROM cs_xs

   The result will not contain “ Native place ” Field . Only reconstruction cs_xs View and then query it , The result will contain “ Native place ” Field .

3. Update the view

   To update the basic table data through the view , You must ensure that the view is updatable . An updatable view can be one of the following ：

  （1） To create a view SELECT There is no aggregate function in the statement , And there's no TOP、GROUP BY、UNION Clause and DISTINCT keyword ;
  （2） To create a view SELECT The statement does not contain columns calculated from the basic table columns ;
  （3） To create a view SELECT Of the statement FROM Clause must contain at least one basic table .
  （4） adopt INSTEAD OF Updatable view created by trigger .

   for example , The view created earlier cs_xs、cs_jy、cs_jy_430 Is an updatable view , and LENDNUM、TOTPRICE Is a non updatable view .

  1． insert data
   Use INSERT sentence View to base table insert data .

  【 example 】 View to computer students cs_xs Insert a new student record in , The library card number is 131180, His name is Kun Kun , Gender is male , The date of birth is 1996-04-29.

INSERT INTO cs_xs( Library card number , full name , Gender , time of birth ,  major , The number of books borrowed )
    VALUES('131180',' Hyk ', 0,'1996-4-29', ' Computer ',0)

   Use SELECT sentence Inquire about cs_xs Basic table based on xs：

SELECT * FROM xs

   You will see that the table has been added (‘131180’, ‘ Hyk ’, 0,‘1996-4-29’, ‘ Computer ’,0,NULL) That's ok .

  2． Modifying data

  【 example 】 View computer majors as cs_xs The library card number is “131180” The student's name was changed to “ Li Jun ”.

UPDATE cs_xs
    SET  full name =' Li Jun '
    WHERE  Library card number ='131180'

  3． Delete data

   Use DELETE sentence You can go through the view Delete the data of the basic table . But to Be careful , For views that depend on multiple base tables （ Partition views are not included ）, Out of commission DELETE sentence . for example , Can't pass on cs_jy Execution view DELETE Statement to delete the related basic table xs And jy The data table .

  【 example 】 Delete computer student view cs_xs The library card number is 131180 The record of .

DELETE FROM cs_xs
    WHERE  Library card number ='131180'

4. Modify the definition of the view

  1． Modify the view through object explorer

   start-up “SQL Server Management Studio, stay “ Object explorer ” To expand “ database ”→“xsbook”→“ View ”→ choice “dbo.cs_xs”, Right click mouse , Choose... From the shortcut menu that pops up “ Design ” A menu item , Enter the view modification window . In this window, it is similar to the window that creates the view , The view structure can be viewed and modified , After modification, click the save icon button .

  2． Use ALTER VIEW Statement modify view
  ALTER VIEW The syntax of the statement is ：

ALTER VIEW [ < Database schema name >. ] < View name > [ ( < Name > [ ,...n ] ) ]
	AS select_statement
	[ WITH CHECK OPTION ]

  【 example 】 take cs_xs The view is modified to include only the library card number of computer majors 、 Name and number of books borrowed .

ALTER VIEW cs_xs
AS
    SELECT  Library card number ,  full name ,  The number of books borrowed 
        FROM xs
        WHERE  major  = ‘ Computer ’

  【 example 】 Modify the view cs_jy Column names contained in ： Library card number 、 full name 、 Cable number 、 And borrowing time

ALTER VIEW cs_jy 
    AS 
    SELECT xs. Library card number , xs. full name ,  Cable number ,  Borrowing time 
        FROM xs,jy
        WHERE xs. Library card number  = jy. Library card number  AND  major  = ' Computer '
    WITH CHECK OPTION

5. Delete view

  1． Delete view through object Explorer

   stay “ Object explorer ” The operation method of deleting a view in is ：
   stay “ View ” Select the view to delete under the directory , Right click mouse , Select... From the shortcut menu that pops up “ Delete ” A menu item , The delete dialog box appears , single click “ determine ” Button , The specified view is deleted .

  2． Use DROP VIEW Statement delete view
   Grammar format ：

DROP VIEW [< Database schema name >. ] < View name > [ ...,n ] [ ; ]

   Use DROP VIEW You can delete one or more views . for example ：

DROP VIEW cs_xs, cs_jy

   The view will be deleted cs_xs and cs_jy.

The cursor

1. The concept of cursors

   An operation on a table T-SQL Statements can usually produce or process a set of records , But many applications , In especial T-SQL Embedded in the main language （ Such as C、VB、PowerBuilder Or other development tools ） Generally, the whole result set cannot be treated as a unit , These applications need a mechanism to ensure that one or more lines in the result set are processed at a time , The cursor （cursor） It provides this mechanism .
  SQL Server Cursors provide the ability to process a result set row by row , A cursor can be thought of as a special pointer , It is associated with a query result , Can point to anywhere in the result set , In order to process the data at the specified location . Using cursors, you can query data and process data at the same time .

2. declare cursor

  1．SQL Standard grammar
   stay SQL In the standard , declare cursor The format of the statement is ：

DECLARE < You name > [ INSENSITIVE ] [ SCROLL ] CURSOR
	FOR select_statement
	[ FOR { READ ONLY | UPDATE [ OF < Name > [ ,…n ] ] } ]

   The following is an example of SQL Standard cursor declaration ：

DECLARE xs_CUR1 CURSOR
FOR
    	SELECT  Library card number , full name , Gender , time of birth , The number of books borrowed 
        	FROM xs
        	WHERE  major  = ' Computer '
   	 FOR READ ONLY

   The cursor defined by this statement is associated with the query result set of a single table , Is read-only , The cursor can only extract data from beginning to end , Equivalent to the forward only cursor mentioned below .

  2．T-SQL Expand
  T-SQL The format of the extended cursor declaration statement is ：

DECLARE < You name > CURSOR
[ LOCAL | GLOBAL ]                                       				/* Cursor scope */
[ FORWORD_ONLY | SCROLL ]                             			/* Cursor movement direction */
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]        /* cursor type */
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]             /* Access properties */
[ TYPE_WARNING ]                                        				/* Type conversion warning message */
FOR select_statement                                        				/*SELECT Query statement */
[ FOR UPDATE [ OF < Name > [ ,…n ] ] ]                    		/* Modifiable Columns */

   Here is one T-SQL Extended cursor declaration ：

DECLARE xs_CUR2 CURSOR  
    DYNAMIC 
    FOR
    SELECT  Library card number , full name , The number of books borrowed 
        FROM xs
        WHERE  major  = ' Computer '
    FOR UPDATE OF  full name 

   This statement declares a named xs_CUR2 Dynamic cursor , Scroll back and forth , The name column can be modified .

3. Open cursor

   After declaring the cursor , To extract data from a cursor , You have to open the cursor first . stay T-SQL in , Use OPEN sentence Open cursor , The format for ：

OPEN { { [ GLOBAL ] < You name > } | < Cursor variable name > } 

  GLOBAL It means that Global cursors , Otherwise, open the local cursor .
  OPEN sentence Open cursor , Then by executing in DECLARE CURSOR（ or SET cursor_variable） Specified in the statement T-SQL Statement to fill the cursor （ That is, generate the result set associated with the cursor ）.
   for example , sentence ：

OPEN xs_CUR1

   Open cursor xs_CUR1. After the cursor is opened , You can extract the data .

  【 example 】 Define cursors xs_CUR3, Then open the cursor , Output the number of lines .

DECLARE xs_CUR3 CURSOR
LOCAL SCROLL SCROLL_LOCKS
FOR
SELECT  Library card number , full name , The number of books borrowed 
        FROM xs
FOR UPDATE OF  full name 
OPEN xs_CUR3
SELECT ' The cursor xs_CUR3 Number of data lines ' = @@CURSOR_ROWS

   The execution result of the statement is shown in Figure 4.38 Shown .

4. Reading data

   After the cursor is opened , You can use FETCH sentence from Reading data .FETCH The format of the statement is ：

FETCH[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar} ]
    FROM ]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,…n ] ]

  【 example 】 From cursor xs_CUR1 Extract data from .

OPEN xs_CUR1
FETCH NEXT FROM xs_CUR1

   The execution result of the statement is shown in the figure .

  【 example 】 From cursor xs_CUR2 Extract data from .

OPEN xs_CUR2
FETCH FIRST FROM xs_CUR2

• Read the first line of the cursor （ The first line of current behavior ）, The result is shown in the figure .

FETCH NEXT FROM xs_CUR2

• Read next line （ The second line of the current behavior ）, The result is shown in the figure ：

FETCH PRIOR FROM xs_CUR2

• Read the previous line （ The first line of current behavior ）, The result is shown in the figure .

FETCH LAST FROM xs_CUR2

• Read the last line （ The last line of the current behavior ）.

FETCH RELATIVE -2 FROM xs_CUR2

• Read the last two lines of the current line （ The penultimate line of the current behavior ）.
  
  
  
    FETCH sentence The execution status of is saved in Global variables @@FETCH_STATUS in , Its value is 0 That's the last one FETCH Successful implementation ; by -1 Indicates that the row to be read is not in the result set ; by -2 Indicates that the extracted row no longer exists （ have been deleted ）.
     For example, continue to execute the following statement ：

FETCH RELATIVE 3 FROM xs_CUR2
SELECT 'FETCH The implementation of ' = @@FETCH_STATUS

   The execution result is -1.

5. Close cursor

   After the cursor is used , Close it in time . Close the cursor and use CLOSE sentence , The format is ：

CLOSE { { [ GLOBAL ] < You name > } | @< Cursor variable name > }
 for example ：

CLOSE xs_CUR2

   The cursor will be closed xs_CUR2.

6. Delete cursor

   When the cursor is closed , Its definition is still , You can use it when you need it OPEN Statement to open it and then use . If the confirmation cursor no longer needs , Will be Release the system space occupied by its definition , namely Delete cursor . Delete cursor use DEALLOCATE sentence , The format is ：

DEALLOCATE { { [ GLOBAL ] < You name > } | @< Cursor variable name >e }

   for example ：

DEALLOCATE xs_CUR2

   The cursor... Will be deleted xs_CUR2.