create table stu ( s_id int primary key, s_name nvarchar(30) default('霸天虎'), s_sex nchar(3) check(s_sex='女' or s_sex='男') ) create table corsue ( s_id int , c_id int, course float, primary key(s_id,c_id), foreign key(s_id) references stu(s_id) ) insert into stu values('东方不败','女') insert into stu values('令狐冲','男') insert into stu values('左冷禅','男') insert into stu values('任我行','男') insert into stu values('周芷偌','女') insert into stu values('小龙女','女') insert into stu values('杨过','男') insert into stu values('唐伯虎','男') insert into stu values('秋香','女') insert into stu values('祝枝山','男') insert into stu values('石榴','女') insert into corsue values(1,1,96); insert into corsue values(2,2,89); insert into corsue values(3,1,76); insert into corsue values(4,4,59); insert into corsue values(5,1,36); insert into corsue values(6,1,80); insert into corsue values(7,7,99); insert into corsue values(8,1,46); insert into corsue values(9,8,78); insert into corsue values(10,1,63); insert into corsue values(11,1,78); --分页显示 DECLARE @PAGE INT DECLARE @CURRENT INT DECLARE @SQL NVARCHAR(1000) SET @PAGE=4 SET @CURRENT=2 SET @SQL=N'SELECT TOP '+CAST(@PAGE AS NVARCHAR(5)) SET @SQL=@SQL+N'* FROM STU WHERE S_ID NOT IN(' SET @SQL=@SQL+N'SELECT TOP '+CAST((@CURRENT-1)*@PAGE AS NVARCHAR(5)) SET @SQL=@SQL+N' S_ID FROM STU)' EXECUTE(@SQL) ----------------------------------------- SELECT S.S_NAME AS '姓名',C.COURSE AS '成绩' FROM STU S INNER JOIN CORSUE C ON S.S_ID=C.S_ID --创建视图,试图是虚拟表,并没有实际存储数据 CREATE VIEW VW_STU_CORSUE AS SELECT S.S_NAME AS '姓名',C.COURSE AS '成绩' FROM STU S INNER JOIN CORSUE C ON S.S_ID=C.S_ID --执行视图 SELECT * FROM VW_STU_CORSUE --创建索引,用代码创建的索引默认为非聚集索引,一般在常用字段上创建索引 CREATE INDEX IN_NAME ON STU(S_NAME) SELECT * FROM STU WITH(INDEX(IN_NAME)) ------------------------------------------------------- --存储过程:预编译SQL语句的集合 EXEC SP_TABLES 'STU' EXEC SP_COLUMNS 'STU' IF OBJECT_ID('TEST','P') IS NOT NULL DROP PROC TEST GO CREATE PROC TEST @A INT,@B INT,@C INT OUTPUT AS SELECT @C=@A+@B -- DECLARE @RESULT INT --EXEC TEST 12,63,@RESULT OUTPUT EXEC TEST @A=12,@B=63,@C=@RESULT OUTPUT SELECT @RESULT --用存储过程实现分页 IF OBJECT_ID('PAGE','P') IS NOT NULL DROP PROC PAGE GO CREATE PROC PAGE @CURRENTPAGE INT=1, @COUNTOFPAGE INT=5 AS DECLARE @SQL NVARCHAR(1000) SET @SQL=N'SELECT TOP '+CAST(@COUNTOFPAGE AS NVARCHAR(5)) SET @SQL=@SQL+N' * FROM STU WHERE S_ID NOT IN(' SET @SQL=@SQL+'SELECT TOP '+CAST((@CURRENTPAGE-1)*@COUNTOFPAGE AS NVARCHAR(5)) SET @SQL=@SQL+' S_ID FROM STU)' EXEC(@SQL) GO EXEC PAGE 1,5 GO --上述存储过程不通用,如果有十张表的话就要建十个存储过程,所以还应该定义两个变量 --一个为表名,一个为列名 IF OBJECT_ID('PAGE','P') IS NOT NULL DROP PROC PAGE GO CREATE PROC PAGE @CURRENTPAGE INT=1, @COUNTOFPAGE INT=5, @TBALENAME NVARCHAR(30), @COLUMN NVARCHAR(30) AS DECLARE @SQL NVARCHAR(1000) SET @SQL=N'SELECT TOP '+CAST(@COUNTOFPAGE AS NVARCHAR(5)) SET @SQL=@SQL+N' * FROM '+@TBALENAME+N' WHERE ' +@COLUMN+ N' NOT IN(' SET @SQL=@SQL+N'SELECT TOP '+CAST((@CURRENTPAGE-1)*@COUNTOFPAGE AS NVARCHAR(5)) SET @SQL=@SQL+N' '+@COLUMN+N' FROM ' SET @SQL=@SQL+@TBALENAME+N')' EXEC(@SQL) GO EXEC PAGE 2,10,'T_BOOK','BNAME' EXEC PAGE 1,5,'STU','S_ID' ----------------------------------------- /*IF OBJECT_ID('PAGE','P') IS NOT NULL DROP PROC PAGE GO CREATE PROC PAGE @CURRENTPAGE INT=1, @COUNTOFPAGE INT=5, @TBALENAME NVARCHAR(30), @COLUMN NVARCHAR(30), @COUNT INT OUTPUT--总条数 AS DECLARE @SQL NVARCHAR(1000) --求总条数的SQL语句 DECLARE @SQL1 NVARCHAR(500) SET @SQL=N'SELECT TOP '+CAST(@COUNTOFPAGE AS NVARCHAR(5)) SET @SQL=@SQL+N' * FROM '+@TBALENAME+N' WHERE ' +@COLUMN+ N' NOT IN(' SET @SQL=@SQL+N'SELECT TOP '+CAST((@CURRENTPAGE-1)*@COUNTOFPAGE AS NVARCHAR(5)) SET @SQL=@SQL+N' '+@COLUMN+N' FROM ' SET @SQL=@SQL+@TBALENAME+N')' EXEC(@SQL) SET @SQL1=N'SELECT @A=COUNT(*) FROM '+@TBALENAME PRINT @SQL1 EXEC SP_EXECUTESQL @SQL1,'@A INT OUTPUT',@COUNT OUTPUT SELECT @COUNT GO DECLARE @COUNT1 INT EXEC PAGE 1,5,'STU','S_ID',@COUNT1 SELECT @COUNT1*/