SqlServer的笔记

概述declare @yyjcw1  nvarchar(50), @yyjcw2  nvarchar(50),@yyjcw3  nvarchar(50) select @yyjcw1='www.enet.com.cn' ,@yyjcw2='www.cdbaba.cn',@yyjcw3='www.enet.com.cn' print @yyjcw1  print @yyjcw2 print @yyjcw declare @yyjcw1  nvarchar(50),@yyjcw2  nvarchar(50),@yyjcw3  nvarchar(50) select @yyjcw1='www.enet.com.cn',@yyjcw2='www.cdbaba.cn',@yyjcw3='www.enet.com.cn' print @yyjcw1  print @yyjcw2 print @yyjcw3  insert into Students values('2012106','张九','False',20,'高三二班','重庆','2012-01-01 00:00:00') print @@IDentity print @@SERVERname declare @myname nvarchar(50) set @myname='张九' select * from Students where name=@myname and ID=4 declare @a nvarchar(10),@b nvarchar(10),@c nvarchar(10) select @a='1111',@b='2222',@c=@a+@b print @c if exists (select * from dbo.sysobjects where name='teachers') drop table teachers Go create table teachers ( ID int IDentity(201201,1),--自动编号 name nvarchar(10),--教师姓名 age int,--教师年龄 EnTime datetime,--进入学校时间 IsMaarry bit,--是否结婚 tel nvarchar(10)--联系电话 ) SELECT top(200)ID,StuNum,name FROM Students declare @mytable table(ID int,StuNum nvarchar(20),name nvarchar(20)) insert into @mytable SELECT ID,name FROM Students select * from @mytable exec sp_addtype newChar,'char(4)','not null' go declare @a int declare @b int declare @c int set @a=5555 set @b=2222 set @c=3333 declare @temp int if(@a>@b) set @temp=@a else set @temp =@b if(@temp>@c) print @temp else print @c go select *,sex,sex = case when sex='True' then '男' when Sex='False' then '女' end  from Students    go  --跳出循环  declare @mysum int  declare @i int  set @i=1  set @mysum=0  while(@i<101)  begin      print @i      set @mysum=@mysum+@i      set @i=@i+1         if(@i=5)      --break      continue    end  print @mysum    go    declare @a int  select @a=100  flag1:  print @a  select @a=@a+1  while @a<105 goto flag1  print '------------'  print @a  go    --局部临时表  create table #temp_stu  (    num nvarchar(20),   name nvarchar(20)  )  drop table #temp_stu  select StuNum,name into #temp_stu from Students   select * from #temp_stu    insert into #temp_stu values('20120202','张三')  select * from #temp_stu      go    --全局临时表   create table ##temp_stu2  (    num nvarchar(20),   name nvarchar(20)  )  insert into ##temp_stu2 values('20120202','张三')  select * from ##temp_stu2  go    select stuNum,name,sex from Students   select * from Students   go    select ID as 自动编号,StuNum as 学号,name as 姓名 from Students    go    select ID as 自动编号,name as 姓名,chinese as 语文,math as 数学,english as 英语,chinese+math+english as 总分,(chinese+math+english )/3 as 平均分 from Students order by 总分    select ID as 自动编号,(chinese+math+english )/3 as 平均分 from Students order by 总分 desc--降序  select ID as 自动编号,(chinese+math+english )/3 as 平均分 from Students order by 总分 asc--升序    go    select *,name1+name2 as 姓名 from teachers  go    select * from Students  where Age<=20    select * from Students  where Age<>20--不等于  select * from Students  where ID=5    select * from Students  where Age>17 and Sex='True'   select * from Students  where Classname='高三一班' or Sex='False'       select * from Students  where Age<20 and Age >17    select * from Students  where Age between 17 and 20--包括了边界    select * from Students  where Age not between 18 and 19        select * from Students where EnTime between '2012-02-01' and '2012-02-28'    select * from Students where Age in(17,18,19)--查询这些年龄的    select * from Students where Address  in('北京','成都')    select * from Students where Address not in('北京','成都') select * from Students where Classname='高三二班' and Sex is not NulL select * from Students where Classname='高三二班' and Sex is  NulL --限制三个 select top 3 ID as 自动编号,(chinese+math+english )/3 as 平均分 from Students order by 总分 desc--降序 select top 3 ID as 自动编号,(chinese+math+english )/3 as 平均分 from Students order by 总分 asc--升序 select top 3 * from Students order by NEWID() --去除某些关系一样的字段，关键字distinct select distinct StuNum,Sex from Students where Classname='高三二班' order by StuNum  select 查询行号=IDENTITY(int,1,Sex into #rowNum from Students  select *from #rowNum go select * from News where ClassID in (select ID from NewsClass where ParentID=1) go select * from(select top 6 * from (select top 8 * from Students order by ID asc ) as Students1 order by ID desc)as Students2 order by ID asc select * from Students go select * from Students where name like '张%' select * from Students where name like '%九' select * from Students where name like '张%九' select * from Students where name like '张%[^九]'--以张开头，不以九结尾 select * from Students where StuNum like '2012013_' select * from Students where name like '___'--一行代表一个字符 select * from Students where Enname like '[h-r]%'--首字母为h到r的,后面不管 select * from Students where Enname like '[ljg]%' select * from Students where Enname like '%[lyn]' select * from Students where Enname like '%[^lyn]'--末尾字符是非lyn的 select * from Students where Enname like '[^h-r]%' select * from Students where age like '[^1-1]%' select * from Students where age like '[^1-1]_' select * from Students where age like '[^1-4]_' select * from Students where age like '[1-4]_' select * from Students where Enname like '%[100%]%' select * from Students where Enname like '%100%%'--关键字还是100 select * from Students where (Enname+name+Classname  like '%[刚一m]%')--姓名含刚,班级含一,Enname含m select * from Students where english is NulL select *,english2= --如果有成绩,就是原来的成绩,否则就是暂无成绩 case when english IS NulL then '暂无成绩' when english IS not NulL then CAST( english AS Nvarchar(20))--统一成字符类型 end from Students go select CONVERT (nvarchar(20),GETDATE(),105) as 时间格式--获取当前时间,102为日期格式 select CONVERT(datetime,'2012-10-29 21:09:00')as 学习时间--将字符类型转变为时间类型 select rtrim('   夜  莺  教 程   网   www.yyjcw.com      ')as newTitle  --去掉右边的空格 select ltrim('   夜  莺  教 程   网   www.yyjcw.com      ')as newTitle  --去掉左边的空格 select SUBSTRING('夜莺教程网的网址是www.yyjcw.com/tuangou.HTML',2,7)as Title --截取一段字符 select STUFF('夜莺视频网的网址是www.yyjcw.com/tuangou.HTML',3,'教程')as Title--先插入字符再插入字符 declare @yyjcw nvarchar(50) set @yyjcw ='夜莺视频网的网址是www.yyjcw.com/tuangou.HTML' select STUFF(@yyjcw,'教程')as Title--直接插入字符 select LEN(@yyjcw) as Titlecount--查看字符长度 select STUFF('夜莺视频网的网址是www.yyjcw.com/tuangou.HTML','教程')as Title--直接插入字符 select 'ssssdgsadDFHSSDFFDGFdfgggsdf' as Title select LOWER('ssssdgsadDFHSSDFFDGFdfgggsdf') as Title--转换大小写 select upper('ssssdgsadDFHSSDFFDGFdfgggsdf') as Title select *,LOWER (Enname) as newname from Students--将名字全部小写后赋值给newname --去掉字符串中的特殊字符 select  REPLACE('sggssddsg 液晶显示器网 死得更快经历过网 时间考虑过网 sdgg','网','') select  REPLACE('sggssddsg 液晶显示器网 死得更快经历过网 时间考虑过网 sdgg','**') select  charindex ('-','028-88888888') as pos --查询某个字符的位置 select SUBSTRING('028-88888888',charindex ('-','028-88888888'),100) as newTitle --子字符串 select GETDATE() as mytime --取出系统时间 select year(GETDATE()) as mytime --取出年 select month(GETDATE()) as mytime select day(GETDATE()) as mytime select CONVERT(nvarchar(20),8) as mytime --只显示时间 select DATEPART(HOUR,GETDATE()) as mytime --取出小时 select DATEPART(HH,GETDATE()) as mytime --取出小时 select DATEPART(MINUTE,GETDATE()) as mytime --取出分钟 select DATEPART(SECOND,GETDATE()) as mytime --取出小时 select DATEPART(MILliSECOND,GETDATE()) as mytime --取出小时 select DATEname(DW,GETDATE()) as 今天是星期几 --查询是星期几 select DATEname(WW,GETDATE()) as 今天是一年的第几周 select DATEname(WW,'2014-10-29') as 今天是一年的第几周 --确定某个日期是一年的第几周 select DATEname(YY,'2014-10-29') as 年份 select DATEname(m,'2014-10-29') as 月份 select DATEname(d,'2014-10-29') as 日期 select DATEname(DY,'2014-10-29') as 是一年中的第几天 select DATEDIFF(d,'2012-05-06','2014-10-29') as 相差的天数 --两个日期相差的天数 select DATEDIFF(HH,'2012-05-06 12:00:00','2014-10-29') as 相差的小时 --两个日期相差的天数 select DATEDIFF(HH,'2014-10-29') as 相差的小时 --两个日期相差的天数 select EnTime,DATEDIFF(d,EnTime,GETDATE()) as 入学天数 from Students delete from Students where DATEDIFF(YY,GETDATE())>3 --年份相差三年的被删除 select EnTime,dateadd(d,EnTime) as 在原来基础上增加三天 from Students select EnTime,dateadd(HH,EnTime)) as 在原来基础上增加三天又两个小时 from Students select EnTime,dateadd(yy,EnTime)) as 在原来基础上增加三年又两个小时 from Students select name from Students order by ID asc --按姓名的笔画排序 select name from Students order by ID desc --按姓名的笔画排序 select * from Students order by age asc,ID asc --按姓名的笔画排序 select * from Students order by name collate chinese_prc_stroke_cs_as_ks_ws --按姓氏的笔画排序 select * from Students order by name collate chinese_prc_cs_as --按姓氏的音序排序 go --动态排序 declare @myorder int set @myorder=1 --按哪种排序 select * from Students order by case @myorder  when 1 then chinese  when 2 then english  when 3 then math  end desc --倒序排 --子句查询 select SUM(chinese)as 语文总和 from Students --求和函数 select avg(chinese)as 语文平均分 from Students --平均分数 select COUNT(*) as 高三二班 from Students where Classname='高三二班' --统计 select COUNT(Classname ) as 班级个数 from Students --统计班级个数 select COUNT(distinct Classname ) as 不重复班级个数 from Students --统计班级个数 select MIN(age ) from Students  select max(age ) from Students  select * from Students where Age=(select max(age ) from Students) --包含子查询 select Classname,COUNT(Classname) as 班级人数 from Students group by Classname --统计每个班级的人数 select Classname,COUNT(Classname) as 班级人数 from Students group by Classname having COUNT(Classname )>2--显示每个班级的人数大于2的 select Classname,COUNT(Classname) as 班级人数 from Students group by Classname having COUNT(Classname )>2 order by 班级人数 desc--显示每个班级的人数大于2的 总结

以上是内存溢出为你收集整理的SqlServer的笔记全部内容，希望文章能够帮你解决SqlServer的笔记所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。