CREATE TABLE [dbo].[Users](
[StudentID] [INT] IDENTITY(1,1) NOT NULL,
[Name] [VARCHAR](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[StudentID] 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
CREATE TABLE [dbo].[Students](
[id] [INT] IDENTITY(1,1) NOT NULL,
[Name] [VARCHAR](50) NULL,
CONSTRAINT [PK_Students] 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]
--创建触发器 Insert
CREATE trigger tg_InsertStudent
on Students --更新Students时
for INSERT --触发insert方法
as
INSERT dbo.Users(Name)VALUES ( (SELECT TOP 1 Name FROM dbo.Students ORDER BY id desc) ) --更新Students,所触发执行SQL
--当在Students表中添加数据时触发触发器,同时Users标也会更新 (向student表中添加数据时,users中也会添加数据)
INSERT dbo.Students
( Name )
VALUES ( '大雪' -- Name - varchar(50)
)
--创建触发器 Delete
CREATE TRIGGER tg_DeleteStudent
ON Students
FOR DELETE
AS
SELECT Name from deleted
Students表中数据:
删除students表数据,触发器中执行查询删除数据,结果:
--创建触发器Update
CREATE TRIGGER tg_UpdateStudent
ON Students
FOR UPDATE
AS
IF UPDATE(Name)
BEGIN
PRINT '修改的Name'
END;
Students表中数据:
修改students表数据,触发器中打印print,结果:
UPDATE dbo.Students SET Name='修改' WHERE id=14
ALTER PROC [dbo].[proc_PageData] --创建存储
( --声明参数
@pageIndex INT = 1,
@pageSize INT = 10,
@icount INT = 0 OUTPUT --输出参数符合条件总记录数
)
AS
BEGIN
DECLARE @pageCount INT;
DECLARE @straccount NVARCHAR(4000);
SET @pageCount=(@pageIndex-1)*@pageSize;
WITH tt AS (
SELECT ROW_NUMBER() OVER(ORDER BY CreateDate ASC) RowId,* FROM dbo.test
)
SELECT TOP (@pageSize) * FROM tt WHERE RowId>@pageCount;
SELECT @icount=COUNT(1) FROM dbo.test;
END;
GO
https://www.cnblogs.com/doudouxiaoye/p/5804467.html
对于使用存储过程封装业务逻辑的好处,归纳起来有以下几点:
1、执行速度快。因为存储过程不需要解析。预先编译了
2、安全性。避免了sql注入(不是绝对的),避免了暴露表结构和字段
创建表(没有主键):
CREATE TABLE [dbo].[userT](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[name] [NCHAR](10) NULL,
[userid] [INT] NULL
) ON [PRIMARY]
造数据1000000条
DECLARE @i INT = 0
DECLARE @name VARCHAR(50) = '第'
WHILE @i<1000000
BEGIN
SET @i=@i+1
INSERT INTO dbo.userT
( name, userid )
VALUES ( '第'+CONVERT(VARCHAR(50),@i), -- name - nchar(10)
@i -- userid - int
)
END
数据库中现在没有索引
创建索引
CREATE INDEX userid_Index
ON dbo.userT(userid)
表中userid上建立了索引,所以执行计划中是索引查找: