SQL database statement collation (Northwind database)

1.NEWID () random function, random sort use

Use when querying a table for random sorting

SELECT *  from  Products ORDER  by NEWID ()

2. Wildcard characters

(1)% represents one or more characters

SELECT * from Productswhere ProductName like ' A% '

(2) _ represents a single character

SELECT *  from ProductsWHEREProductName like 'Alice mutto_'SELECT *  from ProductsWHEREProductName like '_lice Mutton'SELECT *  from ProductsWHEREProductName like 'ali_e Mutton'

View Code

(3) [] a single character within the specified range

SELECT *  from  Products WHERE  like ' [a,b,c]_[a,b,c]% '

(4) [^] single character not in range

SELECT *  from  Products WHERE  like ' [^a,b,c]%[a,b,c] '

3. ORDER by

A. The column number ・the can be used in order by. If you do not specify ascending (ASC) or Descending (DESC), the default is ascending (ASC) C. You can execute an Order by statement of 4 for up to 16 columns. The field after group by group by must be the same as after the Select

SELECT Productid,productname,SUM  from ProductsWHERE ProductID> Ten GROUP  by Productid,productname  -- consistent with select after havingSUM(UnitPrice) >  - -- aggregation functions such as need to have---> similar to where

(1) with ROLLUP: Each group asks for a result

(2) With CUBE: After each group asks for a result, all of them also ask for a result

5. Temporary tables

(1) Partial temporary table #TABLE

SELECT TOP Ten *  into #PRODUCCT_NEW  from  Products SELECT *  from #PRODUCCT_NEW

(2) Global temp Table # #TABLE

SELECT TOP Ten *  into # #PRODUCCT_NEW  from  Products SELECT *  from # #PRODUCCT_NEW DROP TABLE # #PRODUCCT_NEW

(3) Insert into Table Name Select Column1. From.. Where:

INSERT into Categories
(
CategoryID
, CategoryName
, Description
, picture
)
(SELECT 10,categoryname,--This is a new value of 10
N ' This is a description ', picture
From Categories
WHERE categoryid= ' 1 ')

SET Identity_insert Categories on

6. Update Update ... SET ... From ...

UPDATESET CategoryID=(SELECTTOP1 from Categories)WHERE ProductID=3

7.IF while

(1).

IF condition

BEGIN ... END

ELSE

BEGIN ... END

(2)

While condition

BEGIN

...

END

Declare @i intDeclare @j intDeclare @str varchar(Max)Set @i=9Set @j=1 while(@i>=1)beginSet @str="'  while(@j<=@i)     begin    Set @str=@str+CONVERT(varchar,@i)+'*'+CONVERT(varchar,@j)+'='+CONVERT(varchar,@i*@j)+' '     Set @j=@j+1     End Print @str Set @i=@i-1 Set @j=1End

8. Case-When statement

 Select TOP 1000   [EmployeeID], [LastName] , [FirstName], [Title], case [TitleOfCourtesy] when    ms.   then  '  ms.   "   '    

SELECT TOP +Case [ProductID]<TenThen N'<10'When [ProductID]>Tenand [productid]< -Then N'10> <20'ELSE N'>20'END as [ProductID], [ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice ], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [discontinued] from [Northwind]. [dbo]. [Products]

9.

(1) Substring ()

SELECT SUBSTRING (PRODUCTNAME,0,5) from products WHERE ProductID=5

(2) RTRIM, LTrim, left and right indent

(3) Len length

SELECT LEN (ProductName), ProductName from products WHERE ProductID=5

(4) Right, left () output from the number of sides

SELECT Productname,len (PRODUCTNAME), left (PRODUCTNAME,5), right (PRODUCTNAME,3 ) from the products WHERE ProductID=5

SQL database statement collation (Northwind database)