加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MsSql教程 > 正文

SqlServer的笔记

发布时间：2020-12-12 12:54:45 所属栏目：MsSql教程 来源：网络整理
导读：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',
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的

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同