SQL数据库语句整理（Northwind数据库）

1.NEWID()    随机函数，进行随机排序使用

 查询某个表的随机排序时使用 

SELECT * 
FROM Products
ORDER BY NEWID()

2. 通配符

（1） %  代表一个或者任意多个字符

SELECT * FROM Products
WHERE ProductName LIKE 'A%'

(2)  _ 代表单个字符

SELECT * FROM Products
WHERE ProductName LIKE 'Alice Mutto_'

SELECT * FROM Products
WHERE ProductName LIKE '_lice Mutton'

SELECT * FROM Products
WHERE ProductName LIKE 'Ali_e Mutton'

(3) [ ]    指定范围内的单个字符

SELECT * FROM Products
WHERE ProductName LIKE '[A,B,C]_[A,B,C]%'

(4) [^ ]    不在范围内的单个字符

SELECT * FROM Products
WHERE ProductName LIKE '[^A,B,C]%[A,B,C]'

3. ORDER BY  

•A. Order By 中可以使用列号

•B. 如果没有指定是升序(ASC)，还是降序(DESC)，缺省为升序(ASC)

•C. 可以对多达16个列执行Order By语句

 

4. GROUP BY  GROUP BY 后面的字段必须与SELECT后面的一致

SELECT ProductID,ProductName,SUM(UnitPrice) 
FROM Products
WHERE ProductID>10
GROUP BY ProductID,ProductName  --与select后面保持一致
HAVING SUM(UnitPrice)>20 --聚合函数之类的需要用having--->类似于where

(1) WITH ROLLUP：每一组求一个结果

 

(2)WITH CUBE：每一组求一个结果后，所有的也求一个结果

 

5.临时表

（1）局部临时表 #TABLE    

SELECT TOP 10 * 
INTO #PRODUCCT_NEW
FROM Products

SELECT * FROM #PRODUCCT_NEW

 

（2）全局临时表 ##TABLE

SELECT TOP 10 * 
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,--这是一个新的值10
N'这是一个描述',Picture
FROM Categories
WHERE CategoryID='1')

  SET IDENTITY_INSERT Categories ON

6. UPDATE   UPDATE…SET…FROM…

UPDATE Products 
SET CategoryID=(SELECT TOP 1 CategoryID FROM Categories )
WHERE ProductID=3

 

7.IF WHILE

(1).

IF 条件

  BEGIN … END

     ELSE

  BEGIN … END

 (2)

WHILE 条件

  BEGIN

  …

  END     

Declare @i int
Declare @j int
Declare @str varchar(max)
set @i=9
set @j=1
while(@i>=1)
begin
set @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=1
end

 

8. CASE WHEN 语句

 

SELECT TOP 1000 [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[Title]
      ,CASE    [TitleOfCourtesy] 
      WHEN 'Ms.' THEN 'Ms.先生'
      WHEN 'Mr.' THEN 'Mr.先生'
      ELSE '未命名' 
      END AS TitleOfCourtesy
  FROM [Northwind].[dbo].[Employees]

SELECT TOP 1000 
       CASE WHEN [ProductID]<10 THEN N' <10'
            WHEN [ProductID]>10 and [ProductID]<20 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左右缩进

 

(3) LEN长度

SELECT LEN(ProductName),PRODUCTNAME 
FROM PRODUCTS WHERE ProductID=5

 

(4)RIGHT, LEFT  right()从右边数输出，left（）从左边数输出

SELECT 
PRODUCTNAME,LEN(ProductName), 
LEFT(PRODUCTNAME,5)
,RIGHT(PRODUCTNAME,3)
FROM PRODUCTS WHERE ProductID=5

 

(5)PATINDEX  前面的字符串第一次出现在后面字符串的位置，必须要用%

  SELECT REPLACE([ProductName],'A','2222222222')
  FROM Products
  WHERE PRODUCTID=4

 

(6)REPLACE   替换字符串

  SELECT REPLACE([ProductName],'A','2222222222')
  FROM Products
  WHERE PRODUCTID=4

 

(7)CEILING>=int   FLOOR<=int   ROUND截取位数，四舍五入

  SELECT CEILING(UnitPrice)
  FROM Products

   SELECT CEILING(UnitPrice),FLOOR(UnitPrice)
  ,ROUND(UnitPrice,1)
  FROM Products

 

10.取出数据库中的1-5条数据

  SELECT *  FROM 
  (SELECT *,ROW_NUMBER() OVER (ORDER BY CustomerID ASC) AS num FROM Customers ) AS C
  WHERE C.num BETWEEN 1 AND 5