USE [Hangfire.Highlighter]
GO
/****** Object: Table [dbo].[Users] Script Date: 2018/8/23 10:04:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [PK_dbo.Users] 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
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Users] ON
GO
INSERT [dbo].[Users] ([Id], [Name]) VALUES (1, N'1号嘉宾')
GO
INSERT [dbo].[Users] ([Id], [Name]) VALUES (2, N'2号嘉宾')
GO
INSERT [dbo].[Users] ([Id], [Name]) VALUES (3, N'3号嘉宾')
GO
INSERT [dbo].[Users] ([Id], [Name]) VALUES (4, N'4号嘉宾')
GO
INSERT [dbo].[Users] ([Id], [Name]) VALUES (5, N'5号嘉宾')
GO
SET IDENTITY_INSERT [dbo].[Users] OFF
GO
----------------------------上面为表结构------------------------------
begin
declare cur_user cursor for
(select * from Users)--声明游标
open cur_user --打开游标
declare @id int,@name varchar(100)--定义游标存储变量（oracle中可以直接%rowtype）
fetch first from cur_user into @id,@name --提取游标第一行
while @@FETCH_STATUS=0 --循环，0，Fetch语句成功。-1：Fetch语句失败或行不在结果集中。-2：提取的行不存在。
begin
if @id%2=0 --oracle中为Mod()函数取模
begin
update Users set Name='我的ID为偶数' --这里测试游标使用场景
end
/*
Frist：结果集的第一行
Prior：当前位置的上一行
Next：当前位置的下一行
Last：最后一行
Absoute n：从游标的第一行开始数，第n行。
Relative n：从当前位置数，第n行。
*/
fetch next from cur_user into @id,@name --提取游标下一行，
end
--游标打开后，服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集，同时使用游标也会对某些数据进行封锁。
--所以游标一旦用过，应及时关闭，避免服务器资源浪费。
close cur_user --关闭游标
deallocate cur_user --释放游标
end
-------------------------使用for update更新表数据-----------------------------
begin
declare cur_user_for_update cursor for
select * from Users where Id<100 for update of Name --测试使用for update结合where current of 【游标】 更新数据
open cur_user_for_update --切记打开游标
fetch next from cur_user_for_update
while @@FETCH_STATUS=0
begin
update Users set Name='我是被修改了的' where current of cur_user_for_update
fetch next from cur_user_for_update
end
close cur_user_for_update --关闭游标
deallocate cur_user_for_update --释放游标
end
-------------------------使用@@Cursor_Rows全局游标变量-----------------------------
begin
declare cur_user_for_update scroll
cursor for
select * from Users where Id<10
open cur_user_for_update --切记打开游标
fetch next from cur_user_for_update
select @@CURSOR_ROWS --这里输出返回的行数,定义游标必须为scroll
close cur_user_for_update --关闭游标
deallocate cur_user_for_update --释放游标
end
--------------------------------使用select查询集合插入表--------------------------------
insert into Users
select Convert(varchar,(select max(Id) from Users)+1)+'号嘉宾'
union all
select Convert(varchar,(select max(Id) from Users)+2)+'号嘉宾'
union all
select Convert(varchar,(select max(Id) from Users)+3)+'号嘉宾'
union all
select Convert(varchar,(select max(Id) from Users)+4)+'号嘉宾'
union all
select Convert(varchar,(select max(Id) from Users)+5)+'号嘉宾'
---------------------------------sql其他函数练习----------------------------------------
select App_Name() 'AppName' --Microsoft SQL Server Management Studio - 查询
select @@ERROR 'Error' --返回整型，错误代码
select Newid() 'GUID' --返回guid
select Isnull(null,'') 'Isnull'
select @@IDENTITY '最后标识' --详情参考https://docs.microsoft.com/zh-cn/sql/t-sql/functions/identity-transact-sql?view=sql-server-2017
select SCOPE_IDENTITY() '最后标识'
select Isnumeric('23') '是否整型' --经测试，表达式为数字字符串也认为是numberric。例如'45'
select Nullif('true','true') '是否等价' --如果二者不等价，则返回第一个表达式；否则。返回Null值。可以用于判断两个表达式是否等价
select @@ROWCOUNT '受影响行数' --可以用于判断是否操作成功，详情参阅https://docs.microsoft.com/zh-cn/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-2017
select ROWCOUNT_BIG() '受影响行数' --返回受影响行数，放在select、insert、update之后返回受影响总行数。详情https://docs.microsoft.com/zh-cn/sql/t-sql/functions/rowcount-big-transact-sql?view=sql-server-2017
select Session_User 'dbo' --用户名
select @@Trancount '事务的数量' --用于开启、回滚、提交事务中，回滚、提交事务减1
--Update(colume)只能在触发器中使用
alter trigger trigger_user
on Users
after update ----instead of Update,Insert,Delete
AS
IF (Update(Name)) --在CREATE TRIGGER 语句内部使用 IF UPDATE。判断列是否更新，更新做其他操作
begin
print '修改了Name值'
end
go
--测试
update Users set Name='测试Trigger中的Update()' where Id=2
--------------------------------------------------sql日期和时间函数---------------------------------------------------------
select GETDATE() '当前时间'--获取当前时间函数2018-08-23 14:31:14.860,使用CURRENT_TIMESTAMP也可以获取当前时间
select CURRENT_TIMESTAMP '当前时间'
select Year(Getdate()) '年' --获取日期年部分
select MONTH(Getdate()) '月' --获取日期月部分
select Day(Getdate()) '日' --获取日期日部分
select IsDate('2018-8-23') '是否日期格式' --判断是否日期格式
/*
年 yy, yyyy
季度 qq, q
月 mm, m
年中的日 dy, y
日 dd, d
周 wk, ww
星期 dw, w
小时 hh
分钟 mi, n
秒 ss, s
毫秒 ms
微妙 mcs
纳秒 ns
*/
--DATEPART
select DATEPART(yy,getdate()) 年,
DATEPART(qq,getdate()) 季度,
datepart(mm,getdate()) 月,
DATEPART(dd,getdate()) 日,
DATEPART(wk,getdate()) 周,
DATEPART(dw,getdate()) 星期,--使用DATEPART时候返回的星期是从周天为每周的一天开始的
DATEPART(hh,getdate()) 小时,
DATEPART(mi,getdate()) 分,
DATEPART(ss,getdate()) 秒
--Datename
select Datename(yy,getdate()) 年,
Datename(qq,getdate()) 季度,
Datename(mm,getdate()) 月,
Datename(dd,getdate()) 日,
Datename(wk,getdate()) 周,
Datename(dw,getdate()) 星期,--使用Datename返回具体的星期。例如，星期四
Datename(hh,getdate()) 小时,
Datename(mi,getdate()) 分,
Datename(ss,getdate()) 秒
select SYSDATETIME() '系统日期'
select Sysutcdatetime() '系统UTC时间' --使用国家化的时候需要使用UTC时间
select GETUTCDATE() 'UTC时间'
--增加一年,第一个参数为日期简码，第二个为增量，第三个为日期表达式,日期字符串也是可以隐式转换的
select DATEADD(yy,1,getdate()) 增加1年,
DATEADD(qq,1,getdate()) 增加1季度,
DATEADD(mm,1,getdate()) 增加1月,
DATEADD(dd,1,getdate()) 增加1日,
DATEADD(wk,1,getdate()) 增加1周,
DATEADD(dw,1,getdate()) 增加1星期,
DATEADD(hh,1,getdate()) 增加1小时,
DATEADD(mi,1,getdate()) 增加1分,
DATEADD(ss,1,getdate()) 增加1秒
select DATEDIFF(ms,GETDATE(),DATEADD(dd,1,getdate())) '一天毫秒数' --ss-秒，ms-毫秒
--------------------------------------------------sql字符串函数-----------------------------------------------
select LEFT('sqlserver2014',9) '从左截取字符串'
select RIGHT('sqlserver2014',9) '从有截取字符串'
select LEN('sqlserver2014') '字符长度'
select LOWER('SqlServer2014') '小写',UPPER('SqlServer2014') '大写'
select STR(156465.165464,9,2) '数字转字符串' --说明一下，第一个参数float表达式，第二个表示总长度，第三个为小数点长度。当字符数不足总长度时会前面补空填充
select LTRIM(' SqlServer 2014 ') '去左空格',
RTRIM(' SqlServer 2014 ') '去右空格',
LTRIM(RTRIM(' SqlServer 2014 ')) '去左右空格'--没有去全部空格trim()函数
select REPLACE('sqlserver2014','2014','2008') '替换函数' --第一个原表达式，第二个要替换的表达式，第三个替换表达式
select len(SUBSTRING('sqlserver2014',0,10)) '截取字符串'
select REVERSE('sqlserver2014') '反转字符串'