SQL Server 编程
参考：https://www.cnblogs.com/hoojo/archive/2011/07/21/2112559.html
SQL Server 编程示例
一、变量
1、局部变量
DECLARE @局部变量名 数据类型
【例】创建varchar变量
DECLARE @变量名 varchar(50);
2、变量赋值
SET @变量名=表达式
SELECT @变量名=表达式
【例】创建varchar变量，并赋值
DECLARE @变量名 varchar(50); SET @变量名 = '马云'; PRINT @变量名
【例】查询结果，并给变量赋值
DECLARE @变量名 varchar(50); SELECT @变量名 = a.学生姓名 FROM 学生表 a WHERE 学生姓名='马云' PRINT @变量名
【例】查询结果拼接并存入变量
DECLARE @变量名 VARCHAR(5000); SET @变量名=(SELECT STRING_AGG(a.学生编号,'|') FROM dbo.学生表 a WHERE a.学生编号 like '%1%') --结果，2023050001|2023060001|2023070001 select @变量名 as 学生编号
注：如果select语句返回多个数值，则局部变量取最后一个返回值。
【例】WITH AS短语
--WITH AS短语，也叫做子查询部分，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。 with withName as ( select * from 学生表 ) select * from 成绩表 where 学生编号 in (select 学生编号 from withName)
二、打印信息
PRINT('信息')
三、注释
单行注释：--注释文本
多行注释：/*注释文本*/
四、控制语句
1、批处理（GO)
一个批处理段是由一个或者多个语句组成的一个批处理，之所以叫批处理是因为所有语句一次性被提交到一个SQL实例。
- 批处理是分批提交到SQL Server示例，因此在不同的批处理里局部变量不可访问。
- 在不同批处理中，流程控制语句不能跨批处理。
【例】使用批处理（GO）语句
DECLARE @i int; SET @i = 1; GO --错误提示：必须声明标量变量 "@i" PRINT @i
【例】使用多批处理（GO）语句
DECLARE @i int=1; PRINT @i GO DECLARE @i int=2; PRINT @i GO
2、语句块（BEGIN…END）
语句块是多条Transact-SQL语句组成的代码段，从而可以执行一组Transact-SQL语句，经常与while或if...else组合起来使用，可以相互嵌套。
语法如下：
BEGIN
<SQL语句或程序块>
END
【例】使用BEGIN...END
declare @i int;
set @i = 1;
if @i = 1
begin
print('输出内容')
end
3、条件语句（IF…、IF…ELASE…）
IF...ELSE语句用于在执行一组代码之前进行条件判断，根据判断的结果执行不同的代码。IF...ELSE语句语句对布尔表达式进行判断，如果布尔表达式返回为TRUE，则执行IF关键字后面的语句块；如果布尔表达式返回FALSE，则执行 ELSE关键字后面的语句块。
【例】IF...
declare @i int=0;
if @i = 1
print('输出内容')
--IF之外的语句
print('其他内容')
【例】IF…ELASE
declare @i int=0;
if @i = 1
print('IF输出内容')
else
print('ELSE其他内容')
4、IIF函数
【例】IIF函数在执行过程使用
declare @i varchar(10)='女'; declare @sex varchar(10); select @sex = IIF(@i='男', '先生', '女生') print @sex
【例】IIF函数在查询语句中使用
declare @i varchar(10)='女'; declare @sex varchar(10); select IIF(@i='男', '先生', '女生') as sex
5、多条件语句（CASE）
CASE语句是多条件分支语句，相比IF...ELSE语句，CASE语句进行分支流程控制可以使代码更加清晰，易于理解。CASE语句根据表达式逻辑值的真假来决定执行的代码流程。
【例】查询学生成绩，按照“优、良、中、差”显示
select 学生编号,( case when 学生成绩>=90 then '优' when 学生成绩>=80 then '良' when 学生成绩>=60 then '中' else '差' end ) as '学生成绩' from 成绩表;
6、循环语句（WHILE）
循环语句可以设置重复执行SQL语句或语句块的条件，只要指定的条件为TRUE（条件成立），就重复执行语句。
【例】循环10次输出结果
declare @i int=0; while(@i<10) begin print @i; set @i+=1; end
7、跳转标识（GOTO）
GOTO语句可以使程序无条件跳转到指定的程序执行点，语句标识符可以是数字或者字母的组合，但必须以":"结束
【例】跳转到指定的标识位置再继续执行
DECLARE @i INT=1; GO1: BEGIN PRINT @i SELECT @i=@i+1 END WHILE @i<= 3 GOTO GO1
8、返回语句（RETURN）
返回语句可使程序从批处理、存储过程、触发器中无条件退出，不再执行RETURN之后的任何语句。
【例】存储过程返回值
create procedure proc_num as return 1 --执行存储过程 declare @i int; execute @i= proc_num select @i
9、延期执行（WAITFOR）
waitfor语句用于挂起语句的执行，直到指定的时间点或者指定的时间间隔。
语法如下：
WAITFOR {DELAY<'time'>|TIME<'time'>}
- DELAY：用来设定等待的时间，最多24小时。
- TIME：用来设定等待结束的时间点。
【例】延时5秒，输出
WAITFOR DELAY'00:00:05' PRINT'Hello World'
【例】在5点时，输出
WAITFOR TIME'5:00:00' PRINT'Hello World'
10、异常处理（TRY...CATCH）
语法格式如下：
BEGIN TRY
{sql_statement|statement_block}
END TRY
BEGIN CATCH
{sql_statement|statement_block}
END CATCH
【例】程序异常，跳转到CATCH
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT 'ERR'
END CATCH;
数据库
【例】创建数据库
CREATE DATABASE 数据库名称
【例】删除数据库
DROP DATABASE 数据库名称
【例】分离数据库
exec sp_detach_db @dbname=N'数据库名称'
附加数据库
语法格式如下：
EXEC sp_attach_db @dbname = '你的数据库名', @filename1 = '.mdf文件路径（包缀名）', @filename2 = '.ldf文件路径（包缀名）'
【例】附加数据库
exec sp_attach_db @dbname=N'数据库名称', @filename1=N'D:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\数据库名称.mdf', @filename2=N'D:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\数据库名称_log.ldf'
【例】备份数据库
-- backup database <数据库名称> to disk = '磁盘路径'; backup database SampleDb to disk ='D:\database_backups\SampleDb.bak';
【例】还原数据库，数据库不存在情况下
-- restore database <数据库名称> from disk = '磁盘路径' restore database SampleDb from disk = 'D:\database_backups\SampleDb.bak'
【例】还原数据库，数据库存在的情况下
-- with replace:替换 -- restore database <数据库名称> from disk = '磁盘路径' with replace; restore database 第二单元测试 from disk = 'D:\database_backups\SampleDb.bak' with replace;
数据表
【例】判断表是否存在
IF OBJECT_ID (N'学生表', N'U') IS NOT NULL
BEGIN
PRINT '已存在'
END
ELSE
BEGIN
PRINT '不存在'
END
【例】创建学生表
CREATE TABLE [dbo].[学生表]( [学生编号] [int] IDENTITY(1,1) NOT NULL,-- 学生编号,identity:默认是从1开始自增，每次增加1 [班级编号] [int] NULL, [学生姓名] [varchar](50) NULL, [学生生日] [date] NULL)
【例】重命名表
-- 修改表名 -- 将学生表 修改为 学生信息表 exec sp_rename '学生表', -- 需要修改的表的名称 '学生信息表' -- 新的表名
【例】删除表
-- drop table <表名> drop table 学生表
【例】判断字段是否存在
IF COL_LENGTH('学生表', '学生编号') IS NULL
BEGIN
PRINT '不存在'
END
ELSE
BEGIN
PRINT '不存在'
END
【例】增加字段
alter table 学生表 add 性别 bit -- 添加性别字段
【例】修改字段
alter table 学生表 alter column 性别 char(1)
【例】删除字段
alter table 学生表 drop column 性别
【例】重命名字段
exec sp_rename '学生表.性别', -- 需要修改的字段名称,注意要指定字段所在的表 '学生性别', -- 新的字段名 'column' -- 表示重命名的是列，字段
视图
语法格式如下：
CREATE[ALTER] VIEW 视图名 AS --SELECT T-SQL 语句
【例】创建学生表视图
create view V学生表 as select 学生编号,学生姓名 from 学生表 where 班级编号=1
【例】删除视图
DROP VIEW 视图名
临时表
【例】通过临时表删除数据
DELETE A FROM tMst As A with(rowlock) Inner Join tmpTb As B ON A.code=B.code; DELETE A FROM tDtl As A with(rowlock) Inner Join tmpTb As B ON A.code=B.code;
【例】创建临时表
create table #tmpTb(Id int, Text varchar(64)) insert into #tmpTb select * from dbo.fn_Split(@Ids, ',') s
【例】通过临时表插入数据
Insert into tbMst(code,name) FROM tmpTb
【例】通过链表更新数据
Update A with(rowlock) Set A.Name=B.Name FROM tbMst As A Inner Join tempTb As B With(nolock) ON B.code=A.code
【例】复制数据 及 数据结构，并保存到临时表
--删除临时表 IF object_id(N'tempdb..#tmpTb') is not null DROP TABLE #tmpTb; SELECT * INTO #tmpTb FROM TQmsBarprjHdr With(nolock) WHERE 1=0; --结果 SELECT * FROM #tmpTb;
【例】把临时表的数插入到表
INSERT INTO tbMst SELECT a.* FROM #tmpTb As a; IF object_id(N'tempdb..#tmpTb') is not null DROP TABLE #tmpTb;
游标cursor
游标作用：
- 定位到结果集中的某一行
- 对当前位置的数据进行读写
游标系统变量与函数
游标系统变量与函数返回有关游标的信息。
- @@CURSOR_NUM
返回最后打开的游标中满足条件的元组数。
- @@FETCH_STATUS
【例】查询指定学生数据，并批量更新
----定义游标 declare cursor_Mark cursor scroll for select * from 学生表 where 学生编号=2; --打开游标 open cursor_Mark --检索游标 declare @学生编号 int declare @班级编号 int declare @学生姓名 varchar(50) declare @学生生日 date --遍历第一行 fetch first from cursor_Mark into @学生编号,@班级编号,@学生姓名,@学生生日 while(@@fetch_status=0) begin update 学生表 set 学生生日='2023-10-10' where 学生编号=@学生编号 --遍历下一行 fetch next from cursor_Mark into @学生编号,@班级编号,@学生姓名,@学生生日 end --关闭游标 close cursor_Mark --删除游标 deallocate cursor_Mark
自定义函数
语法格式如下：
CREATE FUNCTION --方法名称(--参数类型) RETURNS 数据类型 AS BEGIN --T-SQL语句 RETURN @变量名 END
1.表值函数
表值函数是用户定义的函数，它返回表类型的数据。表值函数的返回类型是一个表，因此，可以像使用表一样使用表值函数。
【例】把 A|B|C|D|E 拆分成表格
CREATE FUNCTION [dbo].[fn_STRING_UNAGG]
(
@SrcText nvarchar(max), --源字符串
@Separator nvarchar(10)=' ' --分隔符号，默认为空格
)
RETURNS @rtnTable TABLE --输出的数据表
(
[Id] int identity(1,1),
[Text] nvarchar(max)
)
AS
BEGIN
DECLARE @CurIdx int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);
SELECT @CurIdx=1;
WHILE(@CurIdx<=len(@SrcText))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SrcText,@CurIdx);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SrcText)+1;
SELECT @ReturnText=substring(@SrcText,@CurIdx,@NextIndex-@CurIdx);
INSERT INTO @rtnTable([Text]) VALUES(@ReturnText);
SELECT @CurIdx=@NextIndex+1;
END
RETURN;
END
GO
2.标量函数使用
标量函数接受一个或多个参数并返回单个值。
【例】删除匹配的第一个字符或者最后一个字符
CREATE FUNCTION [dbo].[fn_trimchar](@srctext nvarchar(max), @delchar varchar(8))
RETURNS nvarchar(max)
as
begin
declare @rtntext nvarchar(max);
if @srctext = @delchar
set @rtntext = ''
else begin
if left(@srctext,1) = @delchar
set @rtntext = right(@srctext, len(@srctext)-1 );
else
set @rtntext = @srctext;
if right(@rtntext,1) = @delchar
set @rtntext = left(@rtntext, len(@rtntext)-1);
end
return @rtntext;
end
GO
删除函数
【例】删除自定义函数
drop function 函数名
【例】使用事务transaction
- 回滚事务：RollBack transaction
- 提交事务：commit transaction
- 系统事务：@@error
--开始一个事务 begin transaction --声明局部变量 declare @error int; --变量初始值 set @error=0; update 学生表 set 学生生日='2023-10-1' where 学生编号=1; set @error=@error+@@error; update 学生表2 set 学生生日='2023-10-1' where 学生编号=2; set @error=@error+@@error; if(@error<>0) begin --回滚事务 RollBack transaction print'提交失败' end else begin --提交事务 commit transaction print'提交成功' end --查看事务结果 select * from 学生表 where 学生编号=1; select * from 学生表2 where 学生编号=2;
存储过程
常用系统存储过程有：
exec sp_databases; --查看数据库
exec sp_tables; --查看表
exec sp_columns myDT;--查看列
exec sp_helpIndex myDT;--查看索引
exec sp_helpConstraint myDT;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename myDT, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;
【例】系统存储过程示例
--表重命名 exec sp_rename '学生', '学生表'; --列重命名 exec sp_rename '学生表.旧字段', '新字段', 'column'; --查看表信息 exec sp_help '学生表'; --重命名索引 exec sp_rename N'student.idx_cid', N'idx_cidd', N'index'; --查询所有存储过程 select * from sys.objects where type = 'P'; select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
用户自定义存储过程
参考网址：https://www.cnblogs.com/microsoft-zh/p/15580465.html
语法格式如下：
create[alter] proc 存储过程名字 as --T-SQL语句
【例】删除存储过程
drop proc 存储过程名
【例】调用存储过程
exec 存储过程名 参数1,参数2
触发器
语法如下：
create trigger trig_insert on TransInfo for insert as
【例】delete 触发器
IF(OBJECT_ID('TR_DELETE_BOOK','TR') IS NOT NULL )
DROP TRIGGER TR_DELETE_BOOK
GO
CREATE trigger TR_DELETE_BOOK
ON BOOK
FOR DELETE
AS
BEGIN
print'数据备份';
if(object_id('book_back','U') is not null )
insert into book_back select * from deleted;
else
select * into book_back from deleted;
print '备份完成'
end;
【例】update触发器
--判断数据库中,是否存在这个触发器
IF (object_id('tr_update_book','tr') is not null)
drop trigger tr_update_book
go
create trigger tr_update_book
on book
instead of update
as
begin
declare @bid int ;
select @bid = bid from inserted ;
insert into books(bid) values(@bid);
print'插入成功';
end
【例】insert触发器
--判断数据库中,是否存在这个触发器
IF (object_id('tr_insert_book','tr') is not null)
drop trigger tr_insert_book
go
create trigger tr_insert_book
on book
instead of insert
as
begin
declare @bid int ;
select @bid = bid from inserted ;
insert into books(bid) values(@bid);
print'插入成功';
end
