记录使用SQL Server过程中学到的小知识点

A-1. SQL Server获取自增列下一个Id

---

IDENT_CURRENT('TableName')为当前的最大标识值,IDENT_INCR('TableName')为设置的标识值增量, 两者相加即为下一个标识值

SELECT IDENT_CURRENT('TableName') + IDENT_INCR('TableName');

参考链接：https://social.microsoft.com/Forums/zh-CN/07c3a188-5ae1-4623-ba14-0ae2148f5be0/sql-server-id
SQL Docs: https://docs.microsoft.com/en-us/sql/t-sql/functions/data-type-functions-transact-sql

A-2. 去掉字符串中的最后一个字符

---

A-2.1 使用LEFT/SUBSTRING/STUFF(Ref-1，Ref-2)函数

DECLARE @String as VARCHAR(50)
SET @String = '1,2,3,4,5,6,7,8,9,10,'

--使用LEFT函数及LEN/DATALENGTH函数
SELECT @String AS [String], LEFT(@String, LEN(@String) - 1) AS [Substing Without Last Character]
SELECT @String AS [String], LEFT(@String, DATALENGTH(@String) - 1) AS [Substing Without Last Character]

--使用SUBSTRING函数
SELECT @String As [String], SUBSTRING(@String, 1, LEN(@String) - 1) AS [Substing Without Last Character]

--使用STUFF函数
SELECT @String AS [String], STUFF(@String, LEN(@String), 1, '') AS [Substing Without Last Character]

A-2.2 用于实战中的写法: 字符串可能为null或空串

--使用NULLIF(Ref-1)表达式检查
SELECT @String AS [String], LEFT(@String, NULLIF(LEN(@String) - 1, -1)) AS [Substing Without Last Character]

--使用CASE表达式检查
SELECT @String As [String],
     CASE @String WHEN null THEN null 
     ELSE (
         CASE LEN(@String) WHEN 0 THEN @String 
            ELSE LEFT(@String, LEN(@String) - 1) 
         END 
     ) END AS [Substing Without Last Character]

参考链接：https://stackoverflow.com/questions/1256915/remove-the-last-character-in-a-string-in-t-sql

A-3. CASE-WHEN的一些巧妙用法

A-3.1 巧用CASE-WHEN同时验证用户帐号是否存在、密码是否正确

---

DECLARE @un as NVARCHAR(50)
DECLARE @pwd as Nvarchar(100)
SET @un  = 'admin'
SET @pwd = '12345678'

SELECT
    CASE WHEN [Pwd]= @pwd THEN 1 ELSE 0 END AS [IsExist]
FROM [User]
WHERE [LoginName] = @un

简单解释：

• 如果用户名不存在，则查询结果是无数据.
• 如果用户名存在但密码不正确，则查询结果是 0.
• 如果用户名存在且密码正确，则查询结果是 1.

 

A-3.2  在CASE-WHEN中使用IN

SELECT Id,
  CASE WHEN Id IN ('2', '4') THEN 1 ELSE 0 END AS Even 
FROM (Values('1'), ('2'), ('3'), ('4'), ('5')) temp (Id)

 

A-3.3  巧用CASE-WHEN动态调整排序项

SELECT * FROM TableName
ORDER BY CASE WHEN COL1 > 100 THEN COL1 ELSE COL2 END

 

A-4. 随机返回若干行记录

SELECT TOP (5) * FROM TableName ORDER BY NEWID()

 

A-5. WHERE子句中通过派生表使用别名

/** 使用派生表 **/
SELECT * FROM
  (
    SELECT 
      order_num, 
      COUNT(order_num) AS SUM
    FROM [OrderItems]
    GROUP BY order_num
  ) AS ORDERS
WHERE SUM > 4

/** 使用Having子句 **/
SELECT 
  order_num, 
  COUNT(order_num) AS SUM
FROM [OrderItems]
GROUP BY order_num
HAVING COUNT(order_num) > 4

 

A-6. 复制表定义及数据

--复制表定义
SELECT * INTO [TableName2] FROM [TableName1] WHERE 1 = 0

--复制表数据到另一张表(可以添加Where子句)
INSERT INTO [TableName2] SELECT * FROM [TableName1]

--复制表定义及数据(可以添加Where子句)
SELECT * INTO [TableName2] FROM [TableName1]

 

A-7. 执行由字符串拼接而成的SQL语句

---

DECLARE @sqlText nvarchar(max) 
DECLARE @tableName varchar(100)
SET @tableName= 'Employee'

--查询某张表中可以为null的列
SET @sqlText = N'select * from sys.columns where object_id = object_id('''+ @tableName +''') and is_nullable = 1'
Exec (@sqlText)

 

B-1. 列举数据库中的表名, 列名, 列的数据类型及是否可以为null等

---

SELECT 
     TABLE_CATALOG
    ,TABLE_SCHEMA
    ,TABLE_NAME
    ,COLUMN_NAME
    ,DATA_TYPE
    ,IS_NULLABLE
    ,COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS --系统视图
WHERE 
    TABLE_SCHEMA = 'dbo'
    --以下过滤条件根据自身需求取设定
    AND TABLE_NAME = 'TableName'
    AND COLUMN_NAME = 'ColumnName'
ORDER BY
    TABLE_NAME

 

B-2. 查询数据库中所有含有字段名为"ColumnName"的表

---

SELECT 
  a.[Name] AS TableName, 
  b.[Name] AS ColumnName
FROM sysobjects a 
  INNER JOIN syscolumns b ON a.id = b.id
WHERE
  b.Name in ('ColumnName') 
ORDER BY a.Name

 

B-3. 查询数据库中所有表的约束及对应字段

---

SELECT
    tb.TABLE_CATALOG, 
    tb.TABLE_SCHEMA, 
    tb.TABLE_NAME, 
    tc.CONSTRAINT_TYPE, 
    tc.CONSTRAINT_NAME, 
    kc.COLUMN_NAME, 
    col.IS_NULLABLE,
    col.DATA_TYPE,
    CASE DATA_TYPE
        WHEN 'bit'            THEN cast(col.NUMERIC_PRECISION_RADIX as varchar)
        WHEN 'tinyint'        THEN cast(col.NUMERIC_PRECISION_RADIX as varchar)
        WHEN 'smallint'       THEN cast(col.NUMERIC_PRECISION_RADIX as varchar)
        WHEN 'int'            THEN cast(col.NUMERIC_PRECISION_RADIX as varchar) 
        WHEN 'bigint'         THEN cast(col.NUMERIC_PRECISION_RADIX as varchar)
        WHEN 'real'           THEN cast(col.NUMERIC_PRECISION as varchar) + ',' + cast(col.NUMERIC_SCALE as varchar)
        WHEN 'float'          THEN cast(col.NUMERIC_PRECISION as varchar) + ',' + cast(col.NUMERIC_SCALE as varchar)
        WHEN 'money'          THEN cast(col.NUMERIC_PRECISION as varchar) + ',' + cast(col.NUMERIC_SCALE as varchar)
        WHEN 'smallmoney'     THEN cast(col.NUMERIC_PRECISION as varchar) + ',' + cast(col.NUMERIC_SCALE as varchar)
        WHEN 'decimal'        THEN cast(col.NUMERIC_PRECISION as varchar) + ',' + cast(col.NUMERIC_SCALE as varchar)
        WHEN 'numeric'        THEN cast(col.NUMERIC_PRECISION as varchar) + ',' + cast(col.NUMERIC_SCALE as varchar)
        WHEN 'date'           THEN cast(col.DATETIME_PRECISION as varchar)
        WHEN 'time'           THEN cast(col.DATETIME_PRECISION as varchar)
        WHEN 'smalldatetime'  THEN cast(col.DATETIME_PRECISION as varchar)
        WHEN 'datetime'       THEN cast(col.DATETIME_PRECISION as varchar)
        WHEN 'datetime2'      THEN cast(col.DATETIME_PRECISION as varchar)
        WHEN 'datetimeoffset' THEN cast(col.DATETIME_PRECISION as varchar)
        ELSE                       cast(col.CHARACTER_MAXIMUM_LENGTH as varchar) 
    END AS MAX_LENGTH_OR_PRECISION,
    kc.ORDINAL_POSITION,
    rc.UNIQUE_CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    RIGHT JOIN INFORMATION_SCHEMA.TABLES tb ON tc.TABLE_NAME = tb.TABLE_NAME
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc ON tc.CONSTRAINT_NAME = kc.CONSTRAINT_NAME
    JOIN INFORMATION_SCHEMA.COLUMNS col ON col.COLUMN_NAME = kc.COLUMN_NAME 
        AND col.TABLE_NAME = kc.TABLE_NAME 
        AND col.TABLE_SCHEMA = kc.TABLE_SCHEMA
    LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
ORDER BY tc.TABLE_NAME, kc.COLUMN_NAME