1  use myDB; 2  insert into student values(‘1134110116‘,‘Tom‘,‘男‘,22,‘上海‘,‘18272986984‘,‘上海‘); 3  insert into student values(‘1134110117‘,‘Tom1‘,‘男‘,23,‘江苏‘,‘18272985173‘,‘南京‘); 4  insert into student values(‘1134110118‘,‘Tom2‘,‘男‘,22,‘上海‘,‘18272983394‘,‘上海‘); 5  insert into student values(‘1134110119‘,‘Tom3‘,‘男‘,21,‘上海‘,‘18272986982‘,‘上海‘); 6  insert into student values(‘1134110120‘,‘张三‘,‘男‘,22,‘江苏‘,‘18272982356‘,‘无锡‘); 7  insert into student values(‘1134110121‘,‘张玲‘,‘女‘,21,‘江苏‘,‘18272987878‘,‘苏州‘); 8  insert into student values(‘1134110122‘,‘张小旭‘,‘男‘,23,‘江苏‘,‘18272986981‘,‘盐城‘); 9  insert into student values(‘1134110123‘,‘李辰‘,‘男‘,21,‘湖北‘,‘18272986985‘,‘武汉‘);10  insert into student values(‘1134110101‘,‘李斯‘,‘男‘,22,‘湖北‘,‘18272986986‘,‘黄冈‘);11  insert into student values(‘1134110102‘,‘李小胖‘,‘男‘,22,‘河北‘,‘18272986986‘,‘邯郸‘);12  insert into student values(‘1134110103‘,‘赵小刚‘,‘男‘,23,‘湖北‘,‘18272986986‘,‘武汉‘);13  insert into student values(‘1134110104‘,‘李诗涵‘,‘女‘,22,‘河北‘,‘18272986986‘,‘石家庄‘);14   15  delete from student;  

 1 select top 3  * from student order by stucode; 2   3  select distinct province from student ; 4   5  --group by 6  select sex ,COUNT(*) ‘人数‘ from student group by sex;  7  select province ,COUNT(*)as ‘人数‘ from student group by province;  8   9  select name from student where name like ‘[张李]_‘; --查找名字为二个字的 姓张 和 姓李 的人 10  11 /* COMPUTE子句使用集合函数在查询的结果集中生成汇总行。COMPUTE BY子句用于增加各列汇总行。12 [Compute13 {14 {Avg | count | max | min | stdev | stdevp| var | varp | sum  (expression)}[,…n]15 [by expressin [,….]]}]16 */17  select * from student order by province compute sum(age);18  select * from student order by province compute avg(age) by province;19  

1 --update2  update student set name=‘helen‘,age=21 where name=‘tom‘;3  update student set name=‘helen‘,age=21,sex=‘女‘ where name like ‘tom%‘;4  update student  set  age = age +1;

 1 /*日期函数*/ 2  SELECT DATEPART(month, GETDATE())  AS  ‘Month Number‘;  -- 12 3  SELECT DATEPART(YYYY, GETDATE())  AS  ‘year‘;  -- 2014 4  SELECT DATEPART(MM ,GETDATE())  AS  ‘Month Number‘;  --12 5  SELECT DATEPART(YEAR ,GETDATE())  AS  ‘year‘;  --2014 6  select GETDATE()as ‘今年‘, DATEADD (YEAR,3,GETDATE()) as ‘三年后‘; --2014-12-11 08:41:21.493   2017-12-11 08:41:21.493 7  select GETDATE();     --2014-12-11 08:40:12.800 8   9  select DATENAME(Weekday,‘2014-12-11‘) as ‘weekday‘; --星期四10  select DATENAME(day,GETDATE()) as ‘day‘; --1111  select DATENAME(YEAR ,GETDATE()) as ‘year‘; --201412  SELECT DATENAME(month, getdate()) AS ‘Month Name‘; --1213  select DATEDIFF(DAY,2014/12/16,2014/12/11);   -- 514  SELECT MONTH(‘2014/12/10‘) as 月, DAY(‘2014/12/10‘) as 日,YEAR(‘2014/12/10‘) 年;15  SELECT MONTH(‘5/29/2003‘) as 月, DAY(‘5/29/2003‘) as 日,YEAR(‘5/29/2003‘) 年;16  17  18  /*数学函数*/19  select  ceiling(13.4),  floor(13.4),  round(13.4567,3);  --14  13   13.457020  --select age as ‘年龄‘ from student where CAST(age as char(20))  like ‘%1‘;21  select REVERSE(name) from student where name like ‘Tom%‘;  --mot22  23  SELECT COL_NAME(OBJECT_ID(‘student‘), 2);  -- name

select name,province from student where province in (select province from student where name=‘张三‘) and name!=‘张三‘; --查找与 张三 籍贯相同的人的姓名 及籍贯;/*自连接*/select t1.name,t1.province from student t1 join student t2 on t1.province=t2.province where t2.name=‘张三‘ and  t1.name!=‘张三‘; --查找与 张三 籍贯相同的人的姓名 及籍贯;   或select t1.name,t1.province from student t1 ,student t2   where t1.province=t2.province and t2.name=‘张三‘ and  t1.name!=‘张三‘; --查找与 张三 籍贯相同的人的姓名 及籍贯;select * from student;

 1 declare @sum int,@count int 2 select @sum=0, @count=1 3 while @count<5 4 begin 5   set @sum=@sum+@count; 6   set @count=@count+1; 7 end 8 select @count,@sum; 9 --print @sum;10    

/*存储过程*/Create  procedure selectBySex asselect * from student where sex=‘男‘--执行exec selectBySexCreate  procedure selectBySexWithInput @sex char(10) asselect * from student where sex=@sex;exec selectBySexWithInput  @sex=‘女‘create procedure selectCountWithOutput  @count int output as declare @boy int ,@girl int select @boy=COUNT(*) from student where sex=‘男‘; select @girl=COUNT(*) from student where sex=‘女‘; select @count=ABS(@boy-@girl); declare @count int Exec selectCountWithOutput @count outputprint @count;--select @count as ‘男女生人数之差‘;