CREATE DATABASE StuMS
GO
USE stuMS
GO
CREATE TABLE Student
(
id INT PRIMARY KEY IDENTITY(1, 1) ,
[name] VARCHAR(50) ,
sex VARCHAR(2) DEFAULT ( '男' )
)
GO
INSERT INTO Student VALUES ('王孟贵','男')
INSERT INTO Student VALUES ('王一贵','男')
INSERT INTO Student VALUES ('王二贵','女')
INSERT INTO Student VALUES ('王三贵','男')
INSERT INTO Student VALUES ('王四贵','女')
INSERT INTO Student VALUES ('王五贵','女')
INSERT INTO Student VALUES ('王六贵','女')
INSERT INTO Student VALUES ('王七贵','男')
INSERT INTO Student VALUES ('王八贵','女')
INSERT INTO Student VALUES ('王九贵','男')
INSERT INTO Student VALUES ('王十贵','男')
INSERT INTO Student VALUES ('王卅贵','女')
SELECT * FROM student
SELECT sex AS 性别 FROM student
SELECT 性别=sex FROM student
SELECT DISTINCT sex FROM student
SELECT COUNT(1) FROM student
SELECT COUNT(sex) FROM student
SELECT COUNT(DISTINCT sex) FROM student
SELECT * FROM student
SELECT TOP 3 * FROM student WHERE id NOT IN (SELECT TOP 5 id FROM student) --返回678
SELECT * FROM (
SELECT *,ROW_NUMBER() OVER(ORDER BY a.id ASC) AS RowNumber FROM student AS a
) AS b WHERE RowNumber BETWEEN 5 AND 10
--分页存储过程
IF EXISTS(SELECT * FROM sysobjects WHERE name='p_student')
DROP PROCEDURE p_student
go
CREATE PROCEDURE p_studnet
@startIndex INT,
@pageSize INT
AS
BEGIN WITH studentList AS(
SELECT *,ROW_NUMBER() OVER(ORDER BY A.id ASC) ROW
FROM student A
)
SELECT ROW,* FROM studentList WHERE ROW BETWEEN @startIndex AND @startIndex*(@pageSize-1)
END
--分页
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
--DROP PROCEDURE [dbo].[Deer_Page]
CREATE PROCEDURE [dbo].[Deer_Page]
(
@startIndex INT ,
@pageSize INT ,
@strSql VARCHAR(5000) ,--查询条件
@TableName VARCHAR(50) ,
@DocCount AS BIT = 1 --0返回记录总数 非0返回记录
)
AS
BEGIN TRAN
IF @DocCount = 0
GOTO GetCount
ELSE
GOTO GetSearch
GetCount:--返回记录总数
DECLARE @SearchSql AS NVARCHAR(4000)
SET @SearchSql = 'select count(1) as Total from ' + @TableName
EXEC sp_executesql @SearchSql
--print @SearchSql
COMMIT TRAN
RETURN
GetSearch:--返回记录
DECLARE @SqlQuery VARCHAR(4000)
SET @SqlQuery = 'SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY O.ID ) Row, * from ' + @TableName
+ ' as O) as temp
WHERE Row BETWEEN ' + CAST(@startIndex AS VARCHAR) + ' and '
+ CAST(@startIndex + @pageSize - 1 AS VARCHAR) + @strsql
---print @SqlQuery
EXECUTE(@SqlQuery)
COMMIT TRAN
EXEC [Deer_Page] 10,3,'','student',1
CREATE proc up_deleteStu
@id int
AS
begin TRAN deleteStu
SAVE TRAN deleteStu
DECLARE @error INT
DELETE FROM student WHERE @id=id
SET @error=@@ERROR
DELETE FROM student WHERE @id=id
SET @error+=@@ERROR
IF(@error<>0)
BEGIN
--某操作出错
--撤消事务(回滚)
PRINT '操作失败'
ROLLBACK TRAN deleteStu
END
ELSE
BEGIN
--全部执行成功
PRINT '删除成功'
COMMIT TRAN deleteStu --提交
END
go
EXEC up_deleteStu 2
SELECT * FROM student
--触发器
CREATE TRIGGER tr_deleteStu
ON student
FOR DELETE
AS
--触发后要执行的一句T-sql
PRINT '你无权删除'
ROLLBACK TRAN
CREATE TRIGGER tr_updateStr
ON student
AFTER UPDATE
AS
--获取修改的用户的姓名
DECLARE @name VARCHAR(20),@len INT
SELECT @name=[name] FROM INSERTED
--判断新用户的长度
SET @len=@name
IF(@len<=6 OR @len>-10)
BEGIN
PRINT '用户名长度6-20'
ROLLBACK TRAN
END
GO
GO
USE stuMS
GO
CREATE TABLE Student
(
id INT PRIMARY KEY IDENTITY(1, 1) ,
[name] VARCHAR(50) ,
sex VARCHAR(2) DEFAULT ( '男' )
)
GO
INSERT INTO Student VALUES ('王孟贵','男')
INSERT INTO Student VALUES ('王一贵','男')
INSERT INTO Student VALUES ('王二贵','女')
INSERT INTO Student VALUES ('王三贵','男')
INSERT INTO Student VALUES ('王四贵','女')
INSERT INTO Student VALUES ('王五贵','女')
INSERT INTO Student VALUES ('王六贵','女')
INSERT INTO Student VALUES ('王七贵','男')
INSERT INTO Student VALUES ('王八贵','女')
INSERT INTO Student VALUES ('王九贵','男')
INSERT INTO Student VALUES ('王十贵','男')
INSERT INTO Student VALUES ('王卅贵','女')
SELECT * FROM student
SELECT sex AS 性别 FROM student
SELECT 性别=sex FROM student
SELECT DISTINCT sex FROM student
SELECT COUNT(1) FROM student
SELECT COUNT(sex) FROM student
SELECT COUNT(DISTINCT sex) FROM student
SELECT * FROM student
SELECT TOP 3 * FROM student WHERE id NOT IN (SELECT TOP 5 id FROM student) --返回678
SELECT * FROM (
SELECT *,ROW_NUMBER() OVER(ORDER BY a.id ASC) AS RowNumber FROM student AS a
) AS b WHERE RowNumber BETWEEN 5 AND 10
--分页存储过程
IF EXISTS(SELECT * FROM sysobjects WHERE name='p_student')
DROP PROCEDURE p_student
go
CREATE PROCEDURE p_studnet
@startIndex INT,
@pageSize INT
AS
BEGIN WITH studentList AS(
SELECT *,ROW_NUMBER() OVER(ORDER BY A.id ASC) ROW
FROM student A
)
SELECT ROW,* FROM studentList WHERE ROW BETWEEN @startIndex AND @startIndex*(@pageSize-1)
END
--分页
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
go
--DROP PROCEDURE [dbo].[Deer_Page]
CREATE PROCEDURE [dbo].[Deer_Page]
(
@startIndex INT ,
@pageSize INT ,
@strSql VARCHAR(5000) ,--查询条件
@TableName VARCHAR(50) ,
@DocCount AS BIT = 1 --0返回记录总数 非0返回记录
)
AS
BEGIN TRAN
IF @DocCount = 0
GOTO GetCount
ELSE
GOTO GetSearch
GetCount:--返回记录总数
DECLARE @SearchSql AS NVARCHAR(4000)
SET @SearchSql = 'select count(1) as Total from ' + @TableName
EXEC sp_executesql @SearchSql
--print @SearchSql
COMMIT TRAN
RETURN
GetSearch:--返回记录
DECLARE @SqlQuery VARCHAR(4000)
SET @SqlQuery = 'SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY O.ID ) Row, * from ' + @TableName
+ ' as O) as temp
WHERE Row BETWEEN ' + CAST(@startIndex AS VARCHAR) + ' and '
+ CAST(@startIndex + @pageSize - 1 AS VARCHAR) + @strsql
---print @SqlQuery
EXECUTE(@SqlQuery)
COMMIT TRAN
EXEC [Deer_Page] 10,3,'','student',1
CREATE proc up_deleteStu
@id int
AS
begin TRAN deleteStu
SAVE TRAN deleteStu
DECLARE @error INT
DELETE FROM student WHERE @id=id
SET @error=@@ERROR
DELETE FROM student WHERE @id=id
SET @error+=@@ERROR
IF(@error<>0)
BEGIN
--某操作出错
--撤消事务(回滚)
PRINT '操作失败'
ROLLBACK TRAN deleteStu
END
ELSE
BEGIN
--全部执行成功
PRINT '删除成功'
COMMIT TRAN deleteStu --提交
END
go
EXEC up_deleteStu 2
SELECT * FROM student
--触发器
CREATE TRIGGER tr_deleteStu
ON student
FOR DELETE
AS
--触发后要执行的一句T-sql
PRINT '你无权删除'
ROLLBACK TRAN
CREATE TRIGGER tr_updateStr
ON student
AFTER UPDATE
AS
--获取修改的用户的姓名
DECLARE @name VARCHAR(20),@len INT
SELECT @name=[name] FROM INSERTED
--判断新用户的长度
SET @len=@name
IF(@len<=6 OR @len>-10)
BEGIN
PRINT '用户名长度6-20'
ROLLBACK TRAN
END
GO