-
1. Sql Server冷知识 (1) 删除表内容的方法 truncate table 表名 (清除表记录,这个快) (2) 列出所有数据库 sp_redatabases (3) 存储过程的参数命名 参数一定要以@开头,以下三个命令皆有效。 alter procedure pro_select_customers @sid int as select * from customers where id=@sid alter procedure pro_select_customers @id int as select * from customers where id=@id alter procedure pro_select_customers @@id int as select * from customers where id=@@id --参数命名无效-- alter procedure pro_select_customers sid int as select * from customers where id=sid (4) 更改表名 sp_rename 't_Entity','t_1' (5) 查看表字段 select * from sys.syscolumns where id=OBJECT_ID('detail') (6) 查看存储过程语句 sp_helptext 'pro_select_customers' (7) 更换表名 alter table web rename to web2 (8) 创建事务 begin transaction select * from t_1 commit (9) 创建函数 第一个返回用returns,第二个return。 create function f_plus (@@num1 int,@@num2 int) returns int as begin return @@num1+@@num2; end--dbo.一定要存在-- select dbo.f_plus (2,44) as num (10) 加上自动增长列 select ROW_NUMBER() over(order by oid)as nums,* from orders (11) 游标的使用 sql server换行相当于; --声明游标-- declare cur_test cursor for select * from dbo.Employees declare @@num int set @@num=0 --打开游标-- open cur_test fetch next from cur_test while @@FETCH_STATUS=0 begin --对变量赋值用set-- set @@num=@@num+1 print @@num fetch next from cur_test end --关闭游标-- close cur_test --释放游标-- deallocate cur_test (12) 更改数据库名称 sp_renamedb ‘旧数据库名‘,’新数据库名‘ (13) 没有fetch offset的情况下如何分页 先把表排个序号,然后根据序号去查询分页 select * from( select ROW_NUMBER() over(order by score) as num,* from dbo.StudentScores ) as tb where num between 11 and 20 支持offset的情况下 --每页十条记录,查询第一页的10条-- select * from tableName offset 0 fetch next 10 rows only; --查询第二页的10条 select * from tableName offset 10 fetch next 10 rows only;