use testselect * from sysobjects where xtype=‘u‘select * from userstruncate table users --删除所有记录，性能高于deleteinsert into users values(1,‘admin‘)goinsert into users values(2,‘mrhu‘)goinsert into users values(3,‘kiss‘)select count(*) as counts from users  --比较truncate操作前后变化gotruncate table usersgoselect count(*) as counts from users  print @@version  --系统版本print @@servername  --服务器名insert into users values(‘a‘,‘a‘) --错误编号goif @@error=245	print ‘insert wrong‘print @@language --版本语言信息print @@datefirst  --一周的第一天从星期几算起truncate table usersprint @@cpu_busycreate table partment  --获取最近添加的标识列的值(	pid int identity(3,10),	pname varchar(20))insert into partment(pname) values(‘技术部‘)print @@identityselect * from partmentdeclare @num int --局部变量set @num=12print @numdeclare @strName varchar(20)select @strName=‘state‘print @strNameselect pid,@strName from partment --??declare @i int --if条件判断set @i=9if (@i>10)	begin		print ‘i morethan 10‘	endelse	begin		print ‘i lessthan 10‘	enddeclare @i int --while循环控制set @i=12--print @iwhile (@i<18)  begin	print @i	set @i=@i+1	if @i<17		continue;	if @i>15		break;  end--使用case分支判断select username,‘管理员‘ as rank from users where username=‘admin‘select username,‘普通用户‘ as rank from users where username=‘mrhu‘select username,case username  when ‘admin‘ then ‘管理员‘  when ‘mrhu‘  then ‘普通用户‘else usernameend as rankfrom users--系统函数print ascii(‘ABC‘) --获取指定字符串中左起第一个字符的ASC码print char(75)  --根据给定的ASC码获取相应的字符print len(‘abcde‘) --获取给定字符串的长度print lower(‘ABCDE‘) --转小写print upper(‘abcde‘) --转大写print ltrim(‘  abc abc d‘) --过滤左空格print rtrim(‘  abc abc  ‘)	--过滤右空格print abs(-123) --绝对值print power(2,3) --2的3次方print rand()*1000  --获取0--1000的随机数print pi() --圆周率print getdate() --系统时间print dateadd(day,-3,getdate()) --3天前时间print dateadd(hh,4,getdate())  --加上4小时，hour/hh,minute/mi,second/ssprint datediff(year,‘2005-1-1‘,getdate()) --指定时间和现在时间的年差 print datediff(mi,‘2005-1-1‘,‘2006-1-1‘)  --minute/mi,second/ssprint ‘abc‘+cast(456 as varchar) --字符串转换合并print ‘abc‘+convert(varchar,456)  --字符串连接要保持类型一致print convert(varchar(12), ‘2005-01-01‘)print year(getdate())  --获取指定时间部分，year,month,dayprint datepart(year,getdate())print datepart(hh,getdate()) --小时print datepart(mi,getdate()) --分钟print datepart(ss,‘2005-2-1 12:30:50‘) --秒print datepart(ms,getdate()) --毫秒print host_id() --返回工作站标识号print host_name() --获取主机名print db_id(‘master‘)  --获取数据库编号print db_name(4) --获取数据库名create table student(	sname varchar(30),	sbirthday datetime  --sbirthday datetime default (getdate()))-- 利用系统函数作为默认值约束alter table student add constraint df_student_sbirthday default (getdate()) for sbirthdayinsert into student(sname) values(‘mrhu‘)insert into student values(‘admin‘,default)select * from studentalter table student drop df_student_sbirthday --删除约束sp_help student --显示表信息select stuff(‘ABCDEF‘,2,1,‘GH‘)as test  --填充函数create function countstudent(@sname varchar(12)) --自定义函数returns int begin	return (select count(*) from student where sname=@sname) endselect dbo.countstudent(‘admin‘) as counts --调用自定义函数select * from sysobjects where xtype=‘FN‘create proc p_countstudent  --存储过程创建as select dbo.countstudent(‘mrhu‘) as countsdrop proc p_countstudentexec p_countstudent sp_help student --查看表结构sp_helptext p_countstudent  --查看存储过程内容 if object_id(‘student2‘) is not null	drop function student2create function student2(@sname varchar(12))  --返回内联表值函数returns tableasreturn(	select * from student where sname=@sname)select * from dbo.student2(‘admin‘) --调用函数create function student3(@sname varchar(12))  --表值函数returns @studentTest table(	用户名 varchar(12),	注册时间 datetime)as begin	insert @studentTest 	select * from student as s where sname=@sname	return endselect * from student3(‘mrhu‘) --调用函数drop function student3sp_helptext studentselect distinct sname from student --剔除重复select * from users where id>all(select id from users where id<3)select * from users where id>=any(select id from users)if exists(select * from users where username=‘mrhu‘)print ‘exists‘elseprint ‘not exists‘select username,id from users where username=‘mrhu‘unionselect ‘合计:‘,sum(id) from usersinsert into users values(4,‘number0‘)insert into users values(5,‘number1‘)insert into users values(6,‘number4‘)insert into users values(7,‘number3‘)insert into users values(8,‘number4‘)sp_help usersselect * from usersupdate users set username=‘number2‘ where id=6declare @str varchar(200)  --执行带变量的sqldeclare @i intset @i=4set @str=‘select top ‘+cast(@i as nvarchar(20))+‘ from users‘--exec(@str)exec sp_executesql @strEXECUTE sp_executesql           N‘select * from users where id=@i‘, --select top @i * from users 出错？？？          N‘@i int‘,		  @i = 4;create proc usersPage  --分页查询模拟测试@CurrentPageSize int,@PageSize int,@CurrentPage intasDeclare @strSql nvarchar(400)set @strSql = ‘select * from  (select top ‘ + convert(nvarchar(4), @CurrentPageSize) + ‘ *   from (select top ‘ + convert(nvarchar(4),(@PageSize * @CurrentPage)) + ‘ * from users) as tt  order by id desc) as stt  order by id‘exec sp_executesql @strSql                                       exec userspage 4,3,2use testselect * from sysobjects where type like ‘%f%‘sp_helptext usersPage sp_helptext ProcTestdeclare cur_exp cursor for select * from users --游标定义open cur_expfetch cur_exp			--提取游标fetch next from cur_exp close cur_expdeallocate cur_exp  --释放游标select @@fetch_status --游标执行状态0(fetch执行成)，-1(执行失败或行不存在)，-2(行不存在)create table Book (	title varchar(50),	price numeric(9,3))insert into Book values(‘book1‘,95.00)insert into Book values(‘book2‘,45.00)insert into Book values(‘book3‘,65.00)insert into Book values(‘book4‘,99.00)update Book set price=155.00 where title=‘book2‘delete  from Bookselect * from Book=====================================================           以下部分需要细看=====================================================--使用冒泡排序找出Book表中最贵的书declare cur_book cursor for select title,price from Book open cur_book declare @title varchar(50)declare @price numeric(9,3)declare @title_temp varchar(50)declare @price_temp numeric(9,3)fetch cur_book into @title,@pricefetch cur_book into @title_temp,@price_tempwhile @@fetch_status=0 begin	if @price<@price_temp	  begin		set @title=@title_temp		set @price=@price_temp	  end	fetch cur_book into @title_temp,@price_temp endclose cur_bookdeallocate cur_bookprint ‘最贵的书是：‘+@title+‘ 价格:‘+convert(varchar(20),@price)--通过存储过程，使用冒泡排序寻找最贵的书create proc Book_GetMaxpriceas declare cur_book cursor for select title,price from Book open cur_book declare @title varchar(50) declare @price numeric(9,3) declare @title_temp varchar(50) declare @price_temp numeric(9,3) fetch cur_book into @title,@price if @@fetch_status<>0  begin	print ‘没有图书记录‘	close cur_book	deallocate cur_book	return  end fetch cur_book into @title_temp,@price_temp if @@fetch_status<>0  begin	print ‘最贵的书是：‘+@title+‘ 价格:‘+convert(varchar(20),@price)	close cur_book	deallocate cur_book	return  end while @@fetch_status=0 begin	if @price<@price_temp	  begin		set @title=@title_temp		set @price=@price_temp	  end	fetch cur_book into @title_temp,@price_temp end close cur_book deallocate cur_book print ‘最贵的书是：‘+@title+‘ 价格:‘+convert(varchar(20),@price)drop proc Book_GetMaxpriceexec Book_GetMaxpriceselect * from usersinsert into users values(10,‘2;5;9‘)delete from users where id=10select * from users where username like ‘%‘+cast(id as varchar(12))+‘%‘create trigger myTriggerselect * from sys.triggersselect * from create trigger mytriggeron studentfor insertas	 												insert into student(sname) values(‘mrhu‘)			drop trigger mytriggersp_helptext mytrigger				alter database test set recursive_triggers off												create table emp_mgr(	Emp int primary key)select * from studentinsert into student(sname) values(‘admin‘)sp_helptrigger student --查看与表相关的触发器select * from usersbegin transactiondelete from users where id=12if @@error <>0	rollback traninsert into users values(10,‘test‘)if @@error<>0	begin		print ‘执行错误！‘		rollback tran	endelsecommit tran