常用 SQL 语句使用的总结

--SQL 语句为表添加字段并设置默认值
alter table Student        --表名
add fee        --添加的字段名
int        --字段类型
not null    --是否为空
default 15        --默认值


--修改字段的数据类型和是否为空
alter table Student        
alter column S_Sex
varchar(10) null 


-- with 把查询出来的表当做源表，但是必须把数据库的兼容性设置为：90+
with t as (select * from Student)

select * from t


-- 为表Student添加一个列
alter table Student
add C_S_Id int null

-- 为表Student新增列添加外键约束
alter table Student        -- 需要建立外键的表名
add constraint C_S_Id        -- 外键约束名
foreign key (C_S_Id)        -- 指定外键表的外键列的列名
references Course(C_Id)    -- 关联表的关联列的列名
go


-- 查询出指定表的外键约束名
select name  
from  sys.foreign_key_columns f 
inner join sys.objects o on f.constraint_object_id=o.object_id 
where f.parent_object_id=object_id('Student')    -- Student 表名


-- 删除指定外键约束名的外键约束
alter table Course drop constraint FK__Course__Stu_Id__15502E78        --FK__Course__Stu_Id__15502E78 外键约束名


--返回一个指定数据库的信息
sp_helpdb Test

--返回所有数据库的信息
sp_helpdb

--返回一个指定数据库对象（即表、视图等）的信息
sp_help Course

--返回所有数据库表对象（即表、视图等）的信息
sp_help


--设置数据库兼容性
ALTER DATABASE Test        --数据库名
SET COMPATIBILITY_LEVEL = 80||90||100    --可以设置为80、90或100
GO


 -- 设置允许将显式值插入表的标识列中
 SET IDENTITY_INSERT Student on
 go
 -- 设置不允许将显式值插入表的标识列中
 SET IDENTITY_INSERT Student off
 go

--on:表示设置为可以手动输入标识列的值
--off:表示设置为不可以手动输入标识列的值


SET NOCOUNT ON
--当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。
--当 SET NOCOUNT 为 OFF 时，返回计数。

SET QUOTED_IDENTIFIER ON
--当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。
--当 SET QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须遵守所有 Transact-SQL 标识符规则。

SET ANSI_NULLS ON
--当SET ANSI_NULLS为ON时，那么SQL语句中，判断为空的写法就要修改为WHERE 字段 IS NULL。
--当SET ANSI_NULLS为OFF时，判断为空的写法就要修改为WHERE 字段=NULL。

-- 为表 Student 的字段 C_Id 添加唯一约束
ALTER TABLE Student                -- 表名
ADD CONSTRAINT UQ_CourseID_Primary_Key    -- 约束名
UNIQUE (C_Id)    -- 指定字段名

-- 为表 Student 的字段 S_Name 添加默认值约束
ALTER TABLE Student    -- 表名
ADD CONSTRAINT DF_S_Name_Default    -- 约束名
DEFAULT('233') FOR S_Name    -- 指定字段名

-- 为表 Student 的字段 C_Id 添加外键约束并设置级联删除、更新

ALTER TABLE Student        -- 表名
ADD CONSTRAINT FK_C_Id_Foreign_Key    -- 约束名
FOREIGN KEY (C_Id)   -- 外键列
REFERENCES Course(C_Id)        -- 指定关联表的关联字段
ON UPDATE CASCADE    -- 设置级联更新
ON DELETE CASCADE    -- 设置级联删除

补充一个添加和删除主键约束和查询指定表的主外键约束名的 sql 语句：

--为已存在的表添加主键
alter table Student        --表名
add constraint S_C_Id    --主键约束名
primary key (S_Id)    --指定要添加主键的列


--根据表名查询表的主外键名
select a.Name as 表名,b.Xtype as 键类型,b.Name as 键名
from sysobjects a,sysobjects b
where a.ID=b.parent_obj and a.name='Student'
and b.Xtype in('F','PK')


--删除指定表的主键约束
alter table Student        --表名
drop constraint PK__Student__A3DFF08D170CE4CD  -- PK__Student__A3DFF08D170CE4CD 主键约束名

 使用 sql 系统存储过程（sp_helpconstraint）根据指定表的表名查询表中的约束。

为指定表添加主键约束，但显式设置为非聚集索引。

-- 根据 指定表名 查询 表的约束
exec sp_helpconstraint UserInfo      -- UserInfo 表名

-- 根据指定主键约束名删除指定表的主键约束
alter table UserInfo drop constraint PK__UserInfo__5A2040BBA6D6767A 

-- 添加主键约束，但设置为非聚集索引
alter table UserInfo add constraint PK__UserInfo__5A2040BBA6D6767A primary key nonclustered (U_Id)

使用 sql 系统存储过程（sp_rename）修改指定表的表名或指定表的字段名。

--指定表 UserInfo 中的字段名 RoleID 修改为 P_Id
exec sp_rename 'UserInfo.RoleID','P_Id'

--指定表名 UserInfo 修改为新表名 UserInfo
exec sp_rename 'UserInfo_Id','UserInfo'

注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。

使用 sql 系统存储过程（sp_helptext）获取自定义存储过程、视图、函数等创建的 T-SQL 文本（不可以用于表）。

  --获取自定义创建存储过程 T-SQL 文本 
  EXEC sp_helptext 'SctStu'
  
  --获取自定义创建视图 T-SQL 文本 
  EXEC sp_helptext 'V_DEMO_Ceshi'
  
  --获取自定义创建函数 T-SQL 文本
  EXEC sp_helptext 'Split'

sql server 按照 in 的值的顺序进行排序：

-- 按照 in 的值进行排序
select * from Student 
where S_StuNo in ('014','003','009')
order by CHARINDEX(','+S_StuNo+',',',014,003,009,')

设置显示或不显示受影响的行数：

-- 设置为 不显示 受影响的行数
set nocount on

-- 设置为 显示 受影响的行数
set nocount off

设置等待执行时间：

select * from Student    --查询第一个表

waitfor delay '0:0:3'    -- 3秒之后执行下一条语句

select * from Course    -- 查询第二个表

使用 RAISERROR 自定义抛出异常：

 --抛出自定义的异常,在最后的catch块中统一处理异常
 RAISERROR(233333,16,3)

参数：

MessageId：

异常的唯一标识，且这个值会被赋值给 SQL Server 的系统变量 @@Error。自定义异常的 MessageId 建议使用 50000 以后的，因为 50000 以内的会被系统异常占用。

Severity：

异常的级别。任何用户都可以指定 0 至 18 的级别。小于 0 的级别被解释为 0，大于 25 的级别被解释为 25。

19 到 25 级别的错误，只能由 sysadmin 角色的成员用 WITH LOG 选项指定。19 到 25 级别的错误，将记录到错误日志和应用程序日志。

20 到 25 级别的错误，被认为是致命的；遇到致命的级别错误，客户端的连接将在收到消息后终止。C# 中的 catch 块中可以俘获 错误级别 11 到 19 级别的异常。

0 至 10 之间的级别不会被 catch 捕获，而 20 到 25 级别的异常，被认为是致命的，会被断开数据库连接。所以 C# cath 块可以接受到这种异常，但异常的内容不是真正的异常内容，可能是类似这样的内容：“在从服务器接收结果时发生传输级错误”。

State：

如果输入负值或大于255 的值会生成错误，产生错误则会中断数据库的连接。

使用 for xml path 把查询的数据转换为 XML 格式。

select * 
from Student 
where S_Id='7' 
for xml path

 查询、删除指定表及指定列的默认值约束，以及删除字段（即列）。

-- 查询指定表的指定列的默认值约束 或 指定表的所有默认值约束
SELECT * 
FROM sys.default_constraints a 
LEFT JOIN sys.sysconstraints b ON a.object_id = b.constid AND a.parent_object_id=b.id
LEFT JOIN sys.columns c ON a.object_id = c.default_object_id
WHERE a.parent_object_id = OBJECT_ID('Student')        -- Student 指定的表名称

AND c.NAME='Flag'    -- Flag 指定的列名称，如果不加这个条件就查询整个表的默认值约束

      -- 删除指定表的默认值约束
      ALTER TABLE Student DROP constraint DF__Stu__Flag__707E9C7C
      
      -- 删除指定表的指定字段（列）
      ALTER TABLE Student DROP COLUMN Flag