Select基础

1.从查询结果中消除重复的记录
select distinct sellid,setcr_name from d_sell

2.选取前面N行数据
select top 10 styleid,sum(set_nums) as '合计' from d_movesub
group by styleid order by '合计' desc
说明:
1.该代码可以查出发货量最大的前10名款式;
2.如不指明desc 默认会按升序排序;

另一种方法:
set rowcount 10      ----限定前面10行,如果设为0将不限定返回的行数
select clothingid,set_nums from d_movesub
order by set_nums desc

3.表达式：
select 15%4 as  Modulo,floor(1.25) as [Floor],ceiling(1.25) as [Ceiling]
执行结果如下：
Modulo         Floor     Ceiling
--------------  -----------  -----------
3                1           2
取模数学运算符(%)只会返回除法运算的余数；
Floor()函数返回小于或等于所给数字表达式的最大整数；
Ceiling()函数返回大于或等于所给数字表达式的最小整数；

select 123+456 as '加法','abc'+'def' as '字符'
返回：
加法             字符
-------------  ---------------
579             abcdef

select '款号:'+styleid,set_nums from d_movesub
说明：字符串与字段也可以合并

4.按位运算符：
‘与’ 用&符号表示  当两个运算对像都为真才会返回真值，如果其中一个对象为假则会返回一个假值；
‘或’ 用|符号表示  如果运算对象中有任何一个为真都会返回一个真值；
‘异或’者用^符号表示  如果两个运算对象中只有一个为真值它就返回一个真值；
‘按位非’ 用~符号表示   不太明白，估计用得很少

5.Case表达式
提示：系统不会为Case表达式提供列名，必须要为每个Case表达式提供列名
5-1.简单Case
select userid,username,case notuse         ----notuse  字段为bit类型，默认值为0
when 1 then '被禁用'  ----如果为1就显示被禁用
when 0 then '启用'      ----如果为0就显示为启用
else '-'                        ----如果都不是就用-表示
end as '有效否'             ----给字段取个别名：有效否
from j_user
返回：
userid    username       有效否
--------------------------------------------------------
1001 系统管理员    启用
1002 蒲少芳         启用
1003 刘友兵       启用
1004 李洁       被禁用
1005 邱礼芳       启用
1006 邓淑华       被禁用
1007 陈来灿       启用
1008 刘思一       被禁用
      ……

5-2布尔Case
select  case  when 1< 0  then  '生活糟透了！'      ----当1<0 就显示生活糟透了，但这是不可能的！
when convert(nvarchar(25),getdate(),111)='2011/09/26'       ----当我刚好三十岁时，那么就提醒我该结婚了！
  then '可以结婚了:)'
when 1>0 then '生活一切正常!'    ----当1>0时就显示生活一切正常
end as '生活状况'               ----取个别名：生活状况
提示：在布尔case中，如果遇到了第一个为真的when 的条件，
就不会再继续对其他when条件进行检查，而是直接返回这个when
条件所对应的then子句的值。

declare @A int,@B int    ---声明变量
set @A=2007                ---给变量赋值
set @B=25
select case
when @A=2007 and @B between 10 and 30 then 1
else NULL
end as Test
返回：
Test
---------
  1
总结：布尔case比简单的case更灵活,不但可以使用不同
的比较运算：< = > 而且还可以处理包含布尔and 和
布尔or的复杂条件

6.Where条件
where 条件对from 子句产生的数据进行筛选，挑出那些要放到结果集中的记录行。
一种提高客户/服务器数据库性能的有效途径是：
让数据库引擎来完成筛选数据的工作，而不要让应用程序来过滤那些无用的数据。
 
描述 运算符 例子   
等于 =    
大于 >    
大于等于 >=    
小于 <    
小于等于 <=    
不等于 <>,!=    
不小于 !<    
不大于 !>    
还提供了4种特殊的运算符 between,in,like,is    
   

6-1  使用Between查找条件
下面的语句查询出了所有加盟商的发货金额，并按金额的升序进行显示；
并且指定只统计2008年的已生效的发货单；
select a.get_mchid,c.shutname,sum(b.s_price*b.set_nums) as '金额'
from d_sell a join d_sellsub b on a.sellid=b.sellid
join j_merchant c on a.get_mchid=c.merchantid
where a.setsure_date between '2008-01-01 00:00:00.001' and '2008-12-31 23:59:58.998'
and a.set_sure=1             ---发货单必需已生效
group by a.get_mchid,c.shutname
order by sum(b.s_price*b.set_nums)
返回：
get_mchid  shutname  金额
----------------------------------------------
JL022    庄河 376.20
FQ014   福清 11249.00
YJ885    延吉 18126.00
XS571    萧山 52314.60
JX012    鸡西 59014.00
      ……

6-2 使用in查找条件
下面的语句查找出所有地区代码为0开头的加盟商：
select merchantid,areaid,shutname from j_merchant
where areaid in (select areaid from j_area where areaid like '0%')
返回：
merchantid  areaid   shutname
----------------------------------------------------------
AS018        006      鞍山
BYQ002      006      鲅鱼圈市
CD006        002      常德
CH111        001      澄海
CS005         002      长沙
CX024         007      慈溪
      ……

6-3 使用Like查找条件
 
描述 通配符 例子   
0个或多个字符 % 'Able' like 'A%'   
单个字符 _ 'Able' like 'Abl_'   
匹配指定范围或集合中的任何单个字符 [] 'a' like '[a-g]'
'a' like '[abcdefg]'   
匹配不属于指定范围或集合的任何单个字符 [^] 'a' like '[^w-z]'
'a' like '[^wxyz]' 

例句：
select * from j_merchant where merchantid like '[A-D]%'
返回merchantid 以 a b c d 4个字母开头的所有记录

查找包含通配符的记录：
A. select * from J_supplier where fullname like '%F[-]15%'
---使用[]注明
B. select * from J_supplier where fullname like '%F&-15%' escape'&'
--使用用escape关键字注明避开通配符,   escape 的意思就是：逃脱, 避开

提示：虽然like运算符非常有用，但它也会对性能造成影响，如果发现你的应用
程序需要频繁地使用like运算符，就应该启用全文索引.

6-4 使用多个where 条件
使用布尔逻辑运算符:and ,or ,not 将多个where 条件组合为一个复杂的where 条件
它们的优先顺序为：and     最高
            or       其次
            not     最低
例句:
select areaid,merchantid from j_merchant
where merchantid like 'A%' or
areaid between '001' and '005' and
merchantid like '%C%'
返回：
areaid   merchantid
--------------------------------
006 AS018  
002 CD006  
001 CH111  
002 CS005  
001 CZ011  
001 MC012  
003 YC717  

对下面的语句做小小的改动就会改变结果
select areaid,merchantid from j_merchant
where （ merchantid like 'A%' or
areaid between '001' and '005'） and
merchantid like '%C%'
加了一对括号就改变了运算符的优先级

6-5 对结果集排序
如果没有指定order by 子句，SQL将按照它获取数据的先后顺序来返回记录，
假如所查询的表具有一个聚集索引，SQL很有可能会按照聚集索引中的键值顺序来返回记录行。
有的时候没有使用order by 子句查询结果看起来也像排序过，但这些只不过是巧合合已。
如果确实需要对数据进行排序，那么应当利用order by 子句来实现它，这才是一种好的开发习惯。
例句：
select userid as '编号',username '姓名' from j_user
order by 姓名
返回：
编号      姓名
--------------------------------
2003 cocopark
1033 仓库二
1007 陈来灿
1019 陈美英
1015 陈伟娜
      ……
注意：
虽然在order by 子句中使用别名，但却不能在where 子句中使用别名。
这是因为在逻辑上where 子句是在查询刚开始的时候执行的，此时还没有完成建立结果列
及其别名的工作，因而不能使用别名；而order by 子句却是最后一步执行的逻辑操作，
此时结果列及其别名都已经创建了，所以它能够使用别名。

使用这个语句可以报告已安装的排序规则，以及服务器的当前排序规则设置
select * from :: fn_helpcollations()

使用下面的语句可以查询出当前服务器的排序规则
select serverproperty('collation') as ServerCollation
返回：
ServerColation
----------------------------------
Chinese_PRC_CI_AS

例句：
select * from j_user
order by userid
Collate Danish_Norwegian_CI_AI       --- 指定排序规则
虽然不是第一个查询都需要排序，但对于那些需要排序的查询来说，将order by 子句
与许多的各种各样的排序规则配合使用，为结果集的排序方式提供了巨大的可能性。

6-6 使用空值
空值代表未知的值，任何包含空值的表达式的计算结果也得为空：
select 1+null
返回
无列名
-----------
 NULL

is null 用来检测空值：
if NULL= NULL       ----如果NULL等于NULL
select '='              ----则返回=
else                     ----否则
select '!='             ----返回!=

返回
--------------
   ！=
该语句返回的值不正确！

if NULL is NULL     
select 'is'
else
select 'is not'

返回
----------
  is

is not 配合使用可以用来检查非空值
select userid,depotid from j_user
 where depotid is not NULL            ----查找到所属店铺不为空的用户
返回
userid    depotid
-----------------------
1001 110
1017 110
1433 104
2001 104
2002 101
     ……

is Null()函数具有两个参数，第一个参数是单个列或者表达式，第二个参数则是一个替代值。
如果第一个参数的值是一个有效值(非空值)，is Null()函数就返回第一个参数，如果第一个参数
的值是空值，is Null()就会返回第二个参数的值来替代空值。
例句：
select userid,isnull(depotid,'空') from j_user
返回
userid    depotid
-----------------------
1001 110
1002 空
1003 空
1017 110
1039 空
1040 空
1433 104
2001 104
     ……

Nullif()    在许多情况下,都需要创建空值来替换那些被用来表示空值的值。如果一个数据库中在应当
使用空值的地方使用了n/a  空字符串或者 -，那么就可以使用nullif()函数将这些不一致的值都替换为空值。
使数据库变得更加完整。

nullif() 函数具有两个参数。如果这两个参数相等，它就会返回一个空值；否则，它会返回前一个参数的值。
为了演示的需要，先使用一个update将部份客户的名称改为空字符串：

update j_merchant
set shutname='    '
where merchantid like 'C%'       ---将客户代码为C开头的客户名称全部改为空格

select merchantid,
case shutname
when '' then 'blank'             ----使用case将为空字符串的名称全改为blank
else shutname                   -----如果不为空字符串就显示原值
end as shutname,
nullif(shutname,'') as shutnamenullif from j_merchant      ----使用nullif()将空字符串改为空值

返回
merchantid          shutname        shutnamenullif
----------------------------------------------------------------------
AS018               鞍山                  鞍山
BYQ002            鲅鱼圈市            鲅鱼圈市
CD001              blank               NULL
CD006              blank               NULL
CH111              blank               NULL
CQ118              blank               NULL
CS005              blank               NULL
CX024              blank               NULL
CY112              blank                NULL
CZ011              blank               NULL
DL117              大连                 大连
DQ459            大庆                  大庆
FQ014             福清                 福清
                   ……