SQL Server Learning Note Series 10

first, write it in front.

Life is a long way, or to stick to go on, their choice of life, it should let such a life radiation wonderful! I do not expect the accumulation now, in the future can harvest how much, at least in the days after the memory, I never gave up, I insisted, I do not regret! Recently talked with friends about the topic of growth, we seem to get rid of the age of being urged, marriage, the next generation is the parents of our expectations, different age to see the way the problem may be different, really to their age, we can really experience that mood, that kind of hope! I just want to tell my parents that we will work hard! Into the future of happiness, is also our pursuit, but in this way, we need greater courage to work hard! You take care, happiness will come!

two. Deadlock

In SQL, a deadlock occurs when we want to access a resource, while the other's resources also want to access the resources you hold. It is like two people holding each other's hands, then two people can not be freed, are bound (deadlock). Let's look at an example:

Open two sessions (numbers 54th, 57th):

Session number 54th:

1 Use TSQLFundamentals2008; 2 BEGIN  TRANSACTION; 3  4 SET unitprice=unitprice+1  5 WHERE  productid=2  6 7 -- prepare to modify data in 57 session in 51st session 8UPDATE  sales.orderdetails  9 SET unitprice=unitprice+1 WHERE productid =2;

Session Number 57th:

1 Use TSQLFundamentals2008;2 BEGIN TRANSACTION;3 4--Update operation gets to exclusive lock5 UPDATE sales.orderdetails6SET unitprice=unitprice+17WHERE ProductID =2;8 9 Ten--Update number 54th session data in session 57th One UPDATE production.products ASET unitprice=unitprice+1 -WHERE productid=2

At this point, the 54th and 57th sessions all do transactional update operations, both of which have their own resources. What if number 54th wants to update the record of number 57th at the same time? Number 57th also want to update the record for session 54th? There will be a deadlock at this point.

We can look at the results of the run:

Through the results we can see that there is a mechanism for the deadlock inside SQL, that is, the choice of deadlock victim, because the operation of the 54th session was sacrificed less cost, so was sacrificed! At this point you can see that number 57th has been updated!

three. T-SQL Programming

1. Defining variables

(1) Constants:

1 DECLARE  @s INT; 2 SET @s=; 3 PRINT @s;

(2) Character type:

1 DECLARE @str NVARCHAR; 2 SET @str ='HelloWorld'; 3 PRINT @str;

The results are printed as follows:

Because the STR is declared as nvarchar, there is no given length, so be sure to define the length of the character, and also use Select to assign a value to the variable:

1 DECLARE @m NVARCHAR (+); 2 SELECT @m=; 3 PRINT @m;

Query the number of customers and save them in a variable:

2. Process Control

(1) If......else

Example: Depending on the current time to decide what to do (sleep or study)

(2) while

Example: Gauss problem

3. Cursors

Cursors are not often used in our use, because cursors are expensive, so for set processing, SQL can be used to solve, as far as possible, not to apply cursors, according to the specific business to be determined.

Now there is a need, we have to query out all the customer company name, and then do other business processing:

One might think of using variable processing to accept the results of the query:

So when variables are not available, consider how to obtain them with cursors.

Use of cursors:

1--1. Declaring a cursor, based on a query2 DECLARE C CURSOR3  for4 SELECT CompanyName5 From sales.customers;6 7DECLARE @name NVARCHAR ( -);8 9--2when using, you must open the cursorTen OPEN C; One  A--3To read data from a cursor, each time a single piece of data can be read - FETCH NEXT from C into @name; -  the--4pay attention to fetch and not necessarily get actual data -While @ @fetch_status =0 - BEGIN - PRINT @name; + FETCH NEXT from C into @name; -  + END; A  at--5when you are done with cursors, be sure to close - CLOSE C; -  ---6. Releasing Cursors -Deallocate C;

Execution Result:

4. Temporary tables

(1) Local temporary table

To create a temporary table, note that the Temp table table name needs to be added (#) before:

1 CREATE TABLE #tempdb 2 (3  num INT4) 5 6  7        (num)8 VALUES  (1), (2), (3 ), (4), (5)

(2) Global temp table

The name is preceded by # #: Those table names that start with two pound numbers (# #). Global temporary tables can be seen on all connections. If these tables are not explicitly dropped before the connection to create the global temporary table is dropped, the tables are dropped as long as all other tasks stop referencing them. When a connection to create a global temporary table is broken, new tasks cannot reference them again. As soon as the current statement is executed, the association between the task and the table is dropped, so the global temporary table is typically dropped whenever the connection to create the global temporary table is broken.

CREATE TABLE # #tempdb (name NVARCHAR)INSERT into # #tempdb         (name) VALUES  ('mm')

5. Dynamic SQL

A dynamic SQL statement that encapsulates a SQL statement into a string record.

First look at the static SQL, that is, the query field determines the query statement is a static SQL statement, such as querying the customer's company name

1 -- static SQL2SELECT CompanyName3 from  

Dynamic sql:

1 DECLARE @sql NVARCHAR (+); 2 SET @sql ='SELECT custid,companyname3 from  sales.customers' ; 4 5 EXEC (@sql);

Execution Result:

One thing to be reminded of is the SQL injection attack, because when the SQL statement is executed, the user's input is evil and there is a loophole. Like what:

1DECLARE @sql NVARCHAR ( -);2SET @sql ='SELECT Custid,companyname3From Sales.customerswhereCustid=';4 5DECLARE @input NVARCHAR ( -);6SET @input ='0; Select * from Sales.customers';7SET @[email protected]+@input;8EXEC (@sql);

Execution Result:

To prevent such a phenomenon from happening, you can execute the command with a strict dynamic SQL statement: sp_executesql

About SQL injection, need to study carefully, here is very simple, hope can further study!

I hope that you Daniel give guidance, inappropriate to accept learning! Thank you!

SQL Server Learning Note Series 10