SQL查询

 

一.数据检索:
(一.属性列操作:)
1.去除重复行:distinct
             select distinct <表名>.<列名> from <[表名]> order by <列名>
2.获取置顶行 :top
             select top 10  *   from   <[表名]>
3.获取函数值:
             select avg<列名>  from <[表名]>
			 select count(*) as '总人数' from <[表名]>
			 select sum(列名) as '总年龄' from <[tstudentinfo]>
			 select case 列名 where '值' then '值' else 列名  end  from <表名>-----替换
			 
			 
			 select top 10 * from humanresources.employee order by hiredate
			 select top 10 percent * from humanresources.employee order by hiredate
			 
(二.条件项操作:)
1.模糊查询:like
             select * from <表名> where <列名> like '%%'
2.范围查询:between...and..			 
			 select * from <表名> where <列名> between 29 and 31
3.空值查询:is null			 
			 select * from <表名>  where <列名> is null
			 select * from <表名>  where <列名> is not null
4.子查询:in或exists
			 select * from <表名> where <列名> in(
			 select * from <表名> s where exists (select * from [表名] u where s.列名=u.列名)
			 
			 select usertname from [tstudentinfo] x,[tuserse] y where x.usertidcard=y.id and y.usersex='女'
			 select usertname from [tstudentinfo] x where x.usertidcard in(select id from [tuserse] y where y.usersex='女')
			 			 
(三.结果集操作)			 
1.汇总查询:group by...having
			 select usersex, count(usersex) from [tusersex] group by usersex
2.排序查询:order by
			 select * from [tstudentinfo] order by userage asc
3.分页查询:top方法			 
			 (查询第11行到第20行)
			 select * from (select top 20 * from [tstudentinfo] order by useridcard asc) x
			 where x.useridcard not in(select top 10 useridcard from [tstudentinfo] order by useridcard asc)
			 (查询前10条数据)
			 declare @fageindex int;
			 set @fageindex =0;
			 select * from (select top (10*(@fageindex+1)) * from [tstudentinfo] order by useridcard asc) x
			 where x.useridcard not in(select top (10*@fageindex) useridcard from [tstudentinfo] order by useridcard asc)
          row_number方法	 
			 declare @fageindex int;
			 set @fageindex =0;
			 select * from (select *,row_number() over(order by useridcard asc) as sqlrowindex from [tstudentinfo])
			 as tablewilthrowindex where sqlrowindex >cast(10*@fageindex as varchar(20)) and rownum< cast((10*(@fageindex+1)+1) as varchar(20))
			 
(四.合并查询:union)
1.条件:
		使用 union 运算符组合的结果集都必须具有相同的结构.
		它们的列名必须相同
		相应的结果集列的数据类型必须兼容
2.特点:
		union 的结果集列名与 union 运算符中第一个 select 语句的结果集中的列名相同
		union 将从运算符中删除重复的行,除非用 all
		sql sever 从左向右对包含 union 运算符的语句求值,可使用()进行求值优先
3.注意:	
	    如果使用 union 运算符,那么各个 select 语句不能包含它们自己的 order by 或 compute 子句
		而只能在最后一个 select 语句的后面使用一个 order by 或 compute 子句:该子句适用于最终的组合结果集
		并且只能在各个 select 语句中指定 group by 和 having 子句
		     例如:
			 select x.username,x.useridcard from [tstudentinfo] x
			 union all
			 select y.id,y.usersex from [tusersex] y  order by x.useridcard desc
			 
(五.连接查询):join
1.自身连接:  (获取表中比'阳'年龄大的学生)
			 select x.username,x.userage from [tstudentinfo] x,[tstudentinfo] y where x.userage>y.userage and y.usertname='阳'
			 
			 declare @age int;
			 select @age=y.userage from [tstudentinfo] y where y.usertname='阳'
			 select x.usertname,x.userage from [tstudentinfo] x where x.userage>@age
2.内连接:(选取的是公共部分,放弃其它部分)
			 select x.usertname,x.userage from [tstudentinfo] x join [tusersex] y on x.useridcard=y.id
			 select x.usertname,x.userage from [tstudentinfo] x inner join [tusersex] y on x.useridcard=y.id
3.外连接:
    a.左连接:(显示左表的所有的数据,并且在右表能匹配的放进去,其它的用NULL来表示)
	         select x.usertname,x.userage from [tstudentinfo] x left outer join [tusersex] y on x.useridcard=y.id
	b.右连接:(显示右表的所有的数据,并且在左表能匹配的放进去,其它的用NULL来表示)
	         select x.usertname,x.userage from [tstudentinfo] x right outer join [tusersex] y on x.useridcard=y.id
	c.全连接:(显示两张表的所有数据,并且将两张表能匹配的放进去,不能匹配的就用NULL来表示)		 
			 select x.usertname,x.userage from [tstudentinfo] x full outer join [tusersex] y on x.useridcard=y.id