sql复习

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