Database June 10, 2014 11:13:10

SQL statement to create the database:

CREATE TABLE student (ID int NOT NULL primary key
, number nvarchar NOT null
, name nvarchar NOT NULL
, brithday DateTime default getdate ()
, adress nvarchar)

Creates a student table with a primary key of Id,not null, and Default GETDATE () indicates that the setting defaults to the current time.

NULL handler function: Select isnull (name, ' Anonymous ') as name from the Employee---the name column has null and becomes anonymous.

Local variables: first declare the re-assignment---DECLARE @ variable name data type;-------declare @name nvarchar (50). There are two ways to assign a value of "1" SET @ variable name = value--------for normal assignment. "2" SELECT @ Variable name = value--------used to query from the table to the data and assign a value---------select @name =title from book where id=2!

Global variables: system variables, global variables must be prefixed with @@ 作为 such as @ @version (version of SQL Server)-----Global variables are defined and maintained by the system, we can only read and cannot modify the values of global variables. [Email protected] @identity, the identity value of the last insertion (SqlHelper has its use).

If else statement in SQL Server:

1 IF(conditional expression)2   BEGIN -----------------------equivalent to the {in C #3Statement 1-----------------------Judgment Statement4     .......5   END --------------------------equivalent to the} in C #6 ELSE7  BEGIN8 Statement 19 ...Ten   END

A while statement in SQL Server:

 1  while  2  begin  -- -----------------    ------------equivalent to the { 3  4   ...  5  break  ------------------------------------can't save!  6  end  --------------------------------equivalent to C #}  

Transaction-Why do I need a transaction?

For example, borrowing questions: Assuming that money goes from A to B, it takes at least two steps: "1" A is reduced by "2" and then the money in B is increased accordingly.

A Program execution unit (unit) that accesses and potentially updates a variety of data items in a database-----that is composed of multiple SQL statements that must be executed as a whole-----These SQL statements are submitted to the system as a whole, either executed or not executed. syntax steps:

1. start transaction : BEGIN TRANSACTION
2. Transaction Commit : Commit TRANSACTION
3. transaction rollback : ROLLBACK TRANSACTION

stored procedures : Just like the methods in the database (functions) and in C #, the stored procedure name/stored procedure parameters/can have a return result. Previously learned if else/while/variables, etc., can be used in stored procedures.

1. Defined by the system, the names that are stored in the master database begin with "sp_" or "xp_".

2. Stored procedures that are created by the user in their own database

1 CREATE  PROC[edure]Stored Procedure name2     @ Parameter 1Data type=Default Value OUTPUT,-------------parameter is the output type3     @ parameter NData type=Default Value OUTPUT4      as----------------------------------------------------followed by code to execute5SQL statements

1. Parameter description: Parameter optional------------parameters are divided into input parameters, output parameters------------input parameters allow default values
2. The EXEC procedure name [parameter] executes the stored procedure-----------.

1 CREATE PROCUsp_getbookbyid2@ Cateidint3          as4               Select *  fromBookwhereCid=@cateid5 6--Execute the stored procedure:7 EXECUsp_getbookbyid5

Trigger : A trigger is a special type of stored procedure that differs from the generic stored procedure ( method ) described earlier. A generic stored procedure is called directly through the stored procedure name , and the trigger is executed primarily by triggering the event .

There are three common triggers: Apply to insert, Update, Delete event, respectively.

1 CREATE TRIGGERTriggername on creating triggers on table-------------------------tables2  for update/insert/DELETE------------------------------Three types of triggers3  as------------------------------------followed by code to execute4 begin-----------------------------------{5.......6 End-------------------------------------}

Instance:

1 CREATE TRIGGERTestforfun ondbo. Category2  for UPDATE3  as4 begin5 Select *  fromafter the book---------------------------------executes the following update code, execute the statement select * from the book6 End7-------------UpdateCategorySetC_name= 'Android2' wherec_id=3-----------Non-trigger code