SQL 基础1

 

 

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

 

select 

select '34'+'34df'; --结果：3434df
select 3/2; -- 1

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

 

update：

 

--update
 update student set name='helen',age=21 where name='tom';
 update student set name='helen',age=21,sex='女' where name like 'tom%';
 update student  set  age = age +1;

 

函数：

/*日期函数*/
 SELECT DATEPART(month, GETDATE())  AS  'Month Number';  -- 12
 SELECT DATEPART(YYYY, GETDATE())  AS  'year';  -- 2014
 SELECT DATEPART(MM ,GETDATE())  AS  'Month Number';  --12
 SELECT DATEPART(YEAR ,GETDATE())  AS  'year';  --2014
 select GETDATE()as '今年', DATEADD (YEAR,3,GETDATE()) as '三年后'; --2014-12-11 08:41:21.493   2017-12-11 08:41:21.493
 select GETDATE();     --2014-12-11 08:40:12.800
 
 select DATENAME(Weekday,'2014-12-11') as 'weekday'; --星期四
 select DATENAME(day,GETDATE()) as 'day'; --11
 select DATENAME(YEAR ,GETDATE()) as 'year'; --2014
 SELECT DATENAME(month, getdate()) AS 'Month Name'; --12
 select DATEDIFF(DAY,2014/12/16,2014/12/11);   -- 5
 SELECT MONTH('2014/12/10') as 月, DAY('2014/12/10') as 日,YEAR('2014/12/10') 年;
 SELECT MONTH('5/29/2003') as 月, DAY('5/29/2003') as 日,YEAR('5/29/2003') 年;
 
 
 /*数学函数*/
 select  ceiling(13.4),  floor(13.4),  round(13.4567,3);  --14  13   13.4570
 --select age as '年龄' from student where CAST(age as char(20))  like '%1';
 select REVERSE(name) from student where name like 'Tom%';  --mot
 
 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;

 

 

 

declare @sum int,@count int
select @sum=0, @count=1
while @count<5
begin
  set @sum=@sum+@count;
  set @count=@count+1;
end
select @count,@sum;
--print @sum;
   

 

 

存储过程：

/*存储过程*/
Create  procedure selectBySex 
as
select * from student where sex='男'
--执行
exec selectBySex


Create  procedure selectBySexWithInput
 @sex char(10) 
as
select * 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 output
print @count;
--select @count as '男女生人数之差';