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 @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,StuNum,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 自动编号,StuNum 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 自动编号,StuNum as 学号,Name as 姓名,chinese as 语文,math as 数学,english as 英语,chinese+math+english as 总分,(chinese+math+english )/3 as 平均分 from Students order by 总分 desc--降序
 select ID as 自动编号,StuNum as 学号,Name as 姓名,chinese as 语文,math as 数学,english as 英语,chinese+math+english 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 自动编号,StuNum as 学号,Name as 姓名,chinese as 语文,math as 数学,english as 英语,chinese+math+english as 总分,(chinese+math+english )/3 as 平均分 from Students order by 总分 desc--降序
select top 3 ID as 自动编号,StuNum as 学号,Name as 姓名,chinese as 语文,math as 数学,english as 英语,chinese+math+english as 总分,(chinese+math+english )/3 as 平均分 from Students order by 总分 asc--升序


select top 3 * from Students order by NEWID()
--去除某些关系一样的字段，关键字distinct
select distinct StuNum,Name,Sex from Students where ClassName='高三二班' order by StuNum 


select 查询行号=IDENTITY(int,1,1),StuNum,Name,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,2,'教程')as title--先插入字符再插入字符


declare @yyjcw nvarchar(50)
set @yyjcw ='夜莺视频网的网址是www.yyjcw.com/tuangou.html'
select STUFF(@yyjcw,3,0,'教程')as title--直接插入字符
select LEN(@yyjcw) as titlecount--查看字符长度


select STUFF('夜莺视频网的网址是www.yyjcw.com/tuangou.html',3,0,'教程')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),GETDATE(),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,'2012-05-06','2014-10-29') as 相差的小时 --两个日期相差的天数
select EnTime,DATEDIFF(d,EnTime,GETDATE()) as 入学天数 from Students
delete from Students where DATEDIFF(YY,EnTime,GETDATE())>3 --年份相差三年的被删除
select EnTime,dateadd(d,3,EnTime) as 在原来基础上增加三天 from Students
select EnTime,dateadd(HH,2,dateadd(d,3,EnTime)) as 在原来基础上增加三天又两个小时 from Students
select EnTime,dateadd(HH,2,dateadd(yy,3,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的