【SQL SERVER】语法复习

一、数据类型 

截图来源：http://www.w3school.com.cn/sql/sql_datatypes.asp

二、数据表操作

1、创建数据表

USE [Test]
GO

/****** Object: Table [dbo].[UserInfo] Script Date: 2018/3/11 13:11:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[UserInfo](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[UserName] [NVARCHAR](50) NOT NULL,
[Pass] [NVARCHAR](100) NOT NULL,
[Tel] [NVARCHAR](50) NULL,
[Email] [NVARCHAR](100) NULL,
[Type] [INT] NOT NULL,
[IsActive] [BIT] NOT NULL,
[CreateBy] [NVARCHAR](100) NOT NULL,
[CreateDate] [DATETIME] NOT NULL,
[UpdateBy] [NVARCHAR](100) NOT NULL,
[UpdateDate] [DATETIME] NOT NULL,
CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

 2、删除数据表

常用于删除临时表。

DROP TABLE dbo.UserInfo;

3、表字段修改

-- 新增字段
ALTER TABLE UserInfo ADD IsDel bit NOT NULL;

-- 删除字段
ALTER TABLE UserInfo DROP COLUMN IsDel;

-- 修改字段类型
ALTER TABLE UserInfo ALTER COLUMN Tel NVARCHAR(50) NOT NULL;

-- 修改字段名
EXEC sp_rename 'UserInfo.Type', 'UserType', 'column';

三、增删改查

SQL Promat 简直是神器，为什么mySql没有相似的工具可用

1、新增

INSERT INTO dbo.UserInfo
(
   UserName, Pass, Tel, Email, UserType, IsActive, CreateBy, CreateDate
)
VALUES
(   
　　N'小可爱', N'112233', N'110', N'xiaokeai@qq.com', 0, 1, N'admin', GETDATE ()
);

2、删除

-- 条件删除
DELETE dbo.UserInfo WHERE IsActive=0;

-- 重置数据，自增主键重新计算
TRUNCATE TABLE dbo.UserInfo;

3、更新

UPDATE dbo.UserInfo SET UserName=N'大可爱' WHERE UserName = N'小可爱';

4、查询

-- 条件查询
SELECT * FROM dbo.UserInfo WHERE IsActive=1;

-- 分组
SELECT UserName FROM dbo.UserInfo GROUP BY UserName;

-- 统计
SELECT COUNT(1) FROM dbo.UserInfo;

-- 排序,默认ASC,顺序
SELECT * FROM dbo.UserInfo ORDER BY Id DESC;

/*
　　JOIN: 如果表中有至少一个匹配，则返回行
　　LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
　　RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
　　FULL JOIN: 只要其中一个表中存在匹配，就返回行
　　INNRT JOIN: 左右表中都存在匹配，才返回行
*/
SELECT  U.*
FROM    dbo.UserInfo U
INNER   JOIN dbo.Customer C ON C.UserId=U.Id
WHERE   U.IsActive=1;

/*
    UNION: 删除结果集中重复的数据
    UNION ALL: 返回所有结果集，包含重复数据
*/
SELECT * FROM dbo.UserInfo WHERE IsActive = 1
UNION
SELECT * FROM dbo.UserInfo WHERE IsActive = 0;

四、其他查询

1、排序有关

RANK()：发生不持续的编号 例如数据值 1,2,2,3 发生的编号将是1,2,2,4

DENSE_RANK()：发生持续的编号 例如数据值 1,2,2,3 发生的编号将是1,2,2,3

ROW_NUMBER()：发生持续的编号(不重复) 例如数据值 1,2,2,3 发生的编号将是1,2,3,4

RANK()和DENSE_RANK()排序的差异就是排序存在并列的情况下。

-- 根据AID排列行号
SELECT  
    ROW_NUMBER() OVER (ORDER BY AID DESC) AS rowid
FROM    bb;

2、分页有关

-- BETWEEN 
SELECT  tab.*
FROM
(
        SELECT  ROW_NUMBER () OVER (ORDER BY u.CreateDate DESC) Rn,
                u.UserName
        FROM    dbo.UserInfo u
        WHERE   u.IsActive=1
) tab
WHERE   tab.Rn BETWEEN 1 AND 10;

-- OFFSET
SELECT  *,
        COUNT(1) OVER (PARTITION BY '') AS Total
FROM    Customers
WHERE   CustomerCity IN ('A-City','B-City')
ORDER BY CustomerID
        OFFSET (@page-1)*@size ROWS 
FETCH NEXT @size ROWS ONLY;

3、判断有关

-- CASE-WHEN判断
SELECT  *
FROM    dbo.UserInfo WITH (NOLOCK)
WHERE   UserType=(CASE
                      WHEN @type=2 THEN
                           N'消费用户'
                  ELSE
                           N'流量用户'
                  END);   

-- Choose函数
SELECT  
        Id ,
        UserName ,
        CHOOSE(IsActive, N'正常', N'无效') AS State
FROM    dbo.UserInfo;    

-- IIF
SELECT  IIF(IsActive=1, N'正常', N'无效') State
FROM    dbo.UserInfo;

-- IF-ELSE
IF (0=0)
   BEGIN
    RETURN 1;
   END;
ELSE
   BEGIN
    RETURN 0
   END;

-- EXISTS ,我理解为“是否存在”
UPDATE  U
SET     U.IsActive=0
FROM    dbo.UserInfo AS U WITH (NOLOCK)
WHERE   NOT EXISTS ( 
               SELECT 1
               FROM   dbo.UserInfo WITH (NOLOCK)
               WHERE  U.Tel IS NOT NULL);

4、循环有关

-- WHILE
DECLARE @i INT; 
SET @i=1;
WHILE @i<=DATEPART(MONTH,GETDATE())
      BEGIN
            SELECT 1
            SET @i=@i+1;
      END;

5、时间格式

-- 2006-05-16 10:57:47 
Select CONVERT(varchar(100), GETDATE(), 20);
-- 2006-05-16 
Select CONVERT(varchar(100), GETDATE(), 23);
-- 10:57:47 
Select CONVERT(varchar(100), GETDATE(), 24);

-- 今天的所有数据
select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=0
-- 昨天的所有数据
select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=1
-- 7天内的所有数据
select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=7
-- 30天内的所有数据
select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=30
-- 本月的所有数据
select * from 表名 where DateDiff(mm,datetime类型字段,getdate())=0
-- 本年的所有数据
select * from 表名 where DateDiff(yy,datetime类型字段,getdate())=0

-- 时间段查询
SELECT  Id,CreatedDatetime,Status
FROM    dbo.Order
WHERE   Status = N'已收货' 
        AND CreatedDatetime >= N'2017-05-31'
        AND CreatedDatetime <=N'2017-06-30'
        AND (CreatedDatetime < N'2017-06-14' OR CreatedDatetime > N'2017-06-20')
ORDER BY CreatedDatetime;

6、统计有关

-- 去重统计
SELECT COUNT(DISTINCT V.PKID)
FROM   #VendorTab V
WHERE  V.Status=N'新建'
        AND V.IsDelivery=0
-- 等同于
SELECT TOP 1 COUNT(1) OVER()
FROM   #VendorTab V
WHERE  V.Status=N'新建'
        AND V.IsDelivery=0
GROUP BY V.PKID

五、其他

1、拼接多行数据为一行

SELECT  STUFF(( SELECT  ',' + Name
                FROM    toponline..T_CourseClass a
                WHERE   a.Id IN ( SELECT    CourseClassId
                                  FROM      toponline..T_MyCourse m
                                  WHERE     m.UserId = u.Id
                                            AND m.IsDel = 0 )
              FOR
                XML PATH('')
              ), 1, 1, '') ClassName
FROM    topmember..T_User u
        LEFT JOIN topmember..T_Person p ON p.Id = u.PersonId
WHERE   u.IsDel = 0;

2、逗号拼接

SELECT  STUFF((SELECT   ','+VenderName
               FROM     dbo.VenderRebateVenderInfo
               WHERE    RuleId=VRI.PKID
              FOR
               XML PATH('')
              ),1,1,'') VenderNames,
        ROW_NUMBER() OVER ( ORDER BY VRI.CreateTime DESC ) AS RowNum ,
        COUNT(1) OVER ( ) AS TotalCount
FROM    dbo.Vender AS VRI WITH ( NOLOCK )
WHERE   1 = 1