SQL Server 编程

参考：https://www.cnblogs.com/hoojo/archive/2011/07/21/2112559.html

SQL Server 编程示例

一、变量
1、局部变量
DECLARE @局部变量名 数据类型
【例】创建varchar变量

?
1
DECLARE @变量名 varchar(50);

 
2、变量赋值
SET @变量名=表达式
SELECT @变量名=表达式

【例】创建varchar变量，并赋值

?
1
2
3
DECLARE @变量名 varchar(50);
SET @变量名 = '马云';
PRINT @变量名

 【例】查询结果，并给变量赋值

?
1
2
3
4
5
DECLARE @变量名 varchar(50);
SELECT @变量名 = a.学生姓名
FROM 学生表 a
WHERE 学生姓名='马云'
PRINT @变量名

 【例】查询结果拼接并存入变量

?
1
2
3
4
5
DECLARE @变量名 VARCHAR(5000);
SET @变量名=(SELECT STRING_AGG(a.学生编号,'|')
FROM dbo.学生表 a WHERE a.学生编号 like '%1%')
--结果，2023050001|2023060001|2023070001
select @变量名 as 学生编号

注：如果select语句返回多个数值，则局部变量取最后一个返回值。
【例】WITH AS短语

?
1
2
3
4
5
6
--WITH AS短语，也叫做子查询部分，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。
with withName as
(
    select * from 学生表
)
select * from 成绩表 where 学生编号 in (select 学生编号 from withName)

 
二、打印信息
 PRINT('信息')

三、注释
单行注释：--注释文本
多行注释：/*注释文本*/

四、控制语句

1、批处理（GO)

一个批处理段是由一个或者多个语句组成的一个批处理，之所以叫批处理是因为所有语句一次性被提交到一个SQL实例。

  • 批处理是分批提交到SQL Server示例，因此在不同的批处理里局部变量不可访问。
  • 在不同批处理中，流程控制语句不能跨批处理。

【例】使用批处理（GO）语句

?
1
2
3
4
5
DECLARE @i int;
SET @i = 1;
GO
--错误提示：必须声明标量变量 "@i"
PRINT @i

 【例】使用多批处理（GO）语句

?
1
2
3
4
5
6
DECLARE @i int=1;
PRINT @i
GO
DECLARE @i int=2;
PRINT @i
GO

 

2、语句块（BEGIN…END）

语句块是多条Transact-SQL语句组成的代码段，从而可以执行一组Transact-SQL语句，经常与while或if...else组合起来使用，可以相互嵌套。

语法如下：

?
1
2
3
BEGIN
          <SQL语句或程序块>
END

 【例】使用BEGIN...END

?
1
2
3
4
5
6
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...

?
1
2
3
4
5
declare @i int=0;
if @i = 1
    print('输出内容')
--IF之外的语句
print('其他内容')

 【例】IF…ELASE

?
1
2
3
4
5
declare @i int=0;
if @i = 1
    print('IF输出内容')
else
    print('ELSE其他内容')

 
4、IIF函数

【例】IIF函数在执行过程使用

?
1
2
3
4
declare @i varchar(10)='女';
declare @sex varchar(10);
select @sex = IIF(@i='男', '先生', '女生')
print @sex

 【例】IIF函数在查询语句中使用

?
1
2
3
declare @i varchar(10)='女';
declare @sex varchar(10);
select IIF(@i='男', '先生', '女生') as sex

5、多条件语句（CASE）

CASE语句是多条件分支语句，相比IF...ELSE语句，CASE语句进行分支流程控制可以使代码更加清晰，易于理解。CASE语句根据表达式逻辑值的真假来决定执行的代码流程。

【例】查询学生成绩，按照“优、良、中、差”显示

?
1
2
3
4
5
6
7
8
select 学生编号,(
    case
        when 学生成绩>=90 then '优' 
        when 学生成绩>=80 then '良' 
        when 学生成绩>=60 then '中' 
        else '差'
    end
) as '学生成绩' from 成绩表;

  
6、循环语句（WHILE）

循环语句可以设置重复执行SQL语句或语句块的条件，只要指定的条件为TRUE（条件成立），就重复执行语句。

【例】循环10次输出结果

?
1
2
3
4
5
6
declare @i int=0;
while(@i<10)
begin
    print @i;
    set @i+=1;
end

 
7、跳转标识（GOTO）

GOTO语句可以使程序无条件跳转到指定的程序执行点，语句标识符可以是数字或者字母的组合，但必须以":"结束

【例】跳转到指定的标识位置再继续执行

?
1
2
3
4
5
6
7
DECLARE @i INT=1;                                    
GO1:
    BEGIN
        PRINT @i
        SELECT @i=@i+1
    END
WHILE @i<= 3 GOTO GO1

 
8、返回语句（RETURN）

返回语句可使程序从批处理、存储过程、触发器中无条件退出，不再执行RETURN之后的任何语句。

【例】存储过程返回值

?
1
2
3
4
5
6
7
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秒，输出

?
1
2
WAITFOR DELAY'00:00:05'
PRINT'Hello World'

 【例】在5点时，输出

?
1
2
WAITFOR TIME'5:00:00'
PRINT'Hello World'


10、异常处理（TRY...CATCH）

语法格式如下：

?
1
2
3
4
5
6
BEGIN TRY
    {sql_statement|statement_block}
END TRY
BEGIN CATCH
    {sql_statement|statement_block}
END CATCH

 【例】程序异常，跳转到CATCH

?
1
2
3
4
5
6
BEGIN TRY
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT 'ERR'
END CATCH;

 

数据库

【例】创建数据库

?
1
CREATE DATABASE 数据库名称

 
【例】删除数据库

?
1
DROP DATABASE 数据库名称

 
【例】分离数据库

?
1
exec sp_detach_db @dbname=N'数据库名称'

 
附加数据库

语法格式如下：

?
1
2
3
4
EXEC sp_attach_db
@dbname = '你的数据库名',
@filename1 = '.mdf文件路径（包缀名）',
@filename2 = '.ldf文件路径（包缀名）'

 

 【例】附加数据库

?
1
2
3
4
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'

 【例】备份数据库

?
1
2
-- backup database <数据库名称> to disk = '磁盘路径';
backup database SampleDb to disk ='D:\database_backups\SampleDb.bak';

 【例】还原数据库，数据库不存在情况下

?
1
2
-- restore database <数据库名称> from disk = '磁盘路径'
restore database SampleDb from disk = 'D:\database_backups\SampleDb.bak'

 【例】还原数据库，数据库存在的情况下

?
1
2
3
-- with replace:替换
-- restore database <数据库名称> from disk = '磁盘路径' with replace;
restore database 第二单元测试 from disk = 'D:\database_backups\SampleDb.bak' with replace;

 

数据表

【例】判断表是否存在

?
1
2
3
4
5
6
7
8
IF OBJECT_ID (N'学生表', N'U') IS NOT NULL
BEGIN
    PRINT '已存在'
END
ELSE
BEGIN
    PRINT '不存在'
END

 【例】创建学生表

?
1
2
3
4
5
CREATE TABLE [dbo].[学生表](
    [学生编号] [int] IDENTITY(1,1) NOT NULL,-- 学生编号,identity:默认是从1开始自增，每次增加1
    [班级编号] [int] NULL,
    [学生姓名] [varchar](50) NULL,
    [学生生日] [date] NULL)

 【例】重命名表

?
1
2
3
4
5
-- 修改表名
-- 将学生表 修改为 学生信息表
exec sp_rename
'学生表', -- 需要修改的表的名称
'学生信息表' -- 新的表名

 【例】删除表

?
1
2
-- drop table <表名>
drop table 学生表

 

【例】判断字段是否存在

?
1
2
3
4
5
6
7
8
IF COL_LENGTH('学生表', '学生编号') IS NULL
BEGIN
　　PRINT '不存在'
END
ELSE
BEGIN
    PRINT '不存在'
END

 【例】增加字段

?
1
2
alter table 学生表
add 性别 bit -- 添加性别字段

 【例】修改字段

?
1
2
alter table 学生表
alter column 性别 char(1)

 【例】删除字段

?
1
2
alter table 学生表
drop column 性别

 【例】重命名字段

?
1
2
3
4
exec sp_rename
'学生表.性别', -- 需要修改的字段名称,注意要指定字段所在的表
'学生性别', -- 新的字段名
'column' -- 表示重命名的是列，字段

 

视图

语法格式如下：

?
1
2
3
CREATE[ALTER] VIEW 视图名
AS
--SELECT T-SQL 语句

 【例】创建学生表视图

?
1
2
3
4
5
create view V学生表
as
select 学生编号,学生姓名
from 学生表
where 班级编号=1

 【例】删除视图

?
1
DROP VIEW 视图名

 

临时表

 【例】通过临时表删除数据

?
1
2
3
4
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;

【例】创建临时表

?
1
2
create table #tmpTb(Id int, Text varchar(64))
insert into #tmpTb select * from dbo.fn_Split(@Ids, ',') s

 【例】通过临时表插入数据

?
1
Insert into tbMst(code,name) FROM tmpTb

 【例】通过链表更新数据

?
1
2
3
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

【例】复制数据 及 数据结构，并保存到临时表

?
1
2
3
4
5
6
7
8
--删除临时表
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;

 【例】把临时表的数插入到表

?
1
2
3
4
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

【例】查询指定学生数据，并批量更新

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
----定义游标
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

自定义函数

语法格式如下：

?
1
2
3
4
5
6
7
CREATE FUNCTION --方法名称(--参数类型)
RETURNS 数据类型
AS
BEGIN
--T-SQL语句
RETURN @变量名
END

 1.表值函数
表值函数是用户定义的函数，它返回表类型的数据。表值函数的返回类型是一个表，因此，可以像使用表一样使用表值函数。

【例】把 A|B|C|D|E 拆分成表格

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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.标量函数使用

标量函数接受一个或多个参数并返回单个值。

【例】删除匹配的第一个字符或者最后一个字符

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

 删除函数

【例】删除自定义函数

?
1
drop function 函数名

 【例】使用事务transaction

  • 回滚事务：RollBack transaction
  • 提交事务：commit transaction
  • 系统事务：@@error
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--开始一个事务
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;

【例】系统存储过程示例

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--表重命名
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

语法格式如下：

?
1
2
3
create[alter] proc 存储过程名字
as
--T-SQL语句

 【例】删除存储过程

?
1
drop proc 存储过程名

 【例】调用存储过程

?
1
exec 存储过程名 参数1,参数2

 

 触发器

语法如下：

?
1
2
3
create trigger trig_insert
on TransInfo for insert
as

【例】delete 触发器

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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触发器

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--判断数据库中,是否存在这个触发器
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触发器

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--判断数据库中,是否存在这个触发器
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

 

posted @   microsoft-zhcn  阅读(80)  评论(0)  编辑  收藏  举报
相关博文：
· SQL Server 查询语句
· SQL Server 数据库操作
· MySQL基础篇快速记忆和查询
· SQLSERVER存储过程基本语法
· SQL语法
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛