SQL知识点汇总

1. UNION 和 UNION ALL 操作符用于合并两个或多个 SELECT 语句的结果集。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

   需要特别注意是：UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

UNION 在表链接后会对所产生的结果集进行排序运算(默认降序,即：a union b ,按照a,b 大小排序显示)，删除重复的记录再返回结果,期间需要排序，如果涉及大量数据操作，性能自然降低，如果确定无重复的，即可使用UNION ALL只是简单的将两个结果合并后就返回。（默认安装 a union all b ,按照 a,b 顺序排序）

 

2.

• JOIN: 如果表中有至少一个匹配，则返回行（即：INNER JOIN 内连接）
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行（左为主表）
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行（右为主表）
• FULL JOIN: 只要其中一个表中存在匹配，就返回行（全部匹配）

3.关于排序的问题：

row_number() OVER (PARTITION BY 字段1 ORDER BY 字段2)
表示根据 字段1分组，在分组内部根据 字段2 排序，而此函数计算的值就表示每组内部 先排序再计算顺

序编号（组内连续的唯一的)

rownum：先对结果集加入伪列rownum然后再进行排序

rank()over(partition by 字段1 order by 字段2)  
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

dense_rank()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的

 

4.关于datepart的使用

DATEPART

返回 指定日期的 指定日期部分的整数。

语法

DATEPART ( datepart , date )
 
其中datepart：应返回的日期部分的参数(yy\m\d\hh\n\s\ms)
date:datetime 或日期格式字符串的表达式

select DATEPART(yy,getdate() )as'年'----- 2011年
select DATEPART(m,getdate() )as'月'-----7月 
select DATEPART(d,getdate() )as'日'-------30日

select CONVERT(varchar(100), GETDATE())as'年月日'---20110730

select substring (CONVERT(varchar(100), GETDATE(), 112),0,7) as'年月'----201107

5.结合 第4中使用substring ，加以详解

   SUBSTRING ( expression, start, length ) (开始0，实际长度)

 

expression
字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。

start
整数或可以隐式转换为 int 的表达式，指定子字符串的开始位置。

length
整数或可以隐式转换为 int 的表达式，指定子字符串的长度（但是结果并补一个返回length字符长度,要看expression的长度和start的设置）。

6.Case具有两种格式

第一种：简单的例如： 根据表达式进行操作

          CASE sex

          WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE '其他'

           END

 

第二种：根据判断或者取值范围进行操作

case

when (0.00<=dd.aaSum and dd.aaSum<=1.00) then 'A'
when (1.00<dd.aaSum and dd.aaSum<=2.00) then 'B'
when (2.00<dd.aaSum and dd.aaSum<=3.00) then 'C'
when (3.00<dd.aaSum and dd.aaSum<=4.00) then 'D'
end

7. sum()聚合函数 对数值型进行求和，如果对字符串进行求和，需要先转化。例如，

  select sum( cast(itemweight as float)) from table1

在表中，itemweight权重是字符型的，需要先转化成float型进行求和。

（特别注意：如果itemweight 查询为null，此时需要用isnull 函数，否则系统报错，无法将DBnull 进行求和

select isnull(sum( cast(itemweight as float)),0) from table1）

(新增：isnull( Max(showSequence),0) 求字段最大最小值是，如果该字段为空，需要isnull)

 

8.结合cast 再总结一下convert 转化

convert，一般转化时间，显示不同格式（详见 http://www.w3school.com.cn/sql/func_convert.asp）

CONVERT(VARCHAR(19),GETDATE())            08 19 2011  7:17PM

在转化时间的时候还有其他格式，可以三个参数，例如：

CONVERT(VARCHAR(10),GETDATE(),110)   110：代表mm-dd-yy    08-19-2011

例如：CONVERT(VARCHAR(10), fromDate, 120)<='2011-09-26' 转化成字符串比较 可以删除时间点的临界归属

此外，还可以转化其他类型：

convert(decimal(12,1),itemscore) itemscore项分数转化成decimal ，1代表保留一位小数，12代表：小数点前面的位数，这个数值很大了。

CAST ( expression AS data_type )即：变量 as 类型 （例如7）

9.having  对Group by 分组后的数据进行筛选（having 必须结合group by 使用）

SELECT aa,SUM(bb) FROM T  WHERE aa='tom' GROUP BY aa HAVING SUM(bb)>10

10.查某一列(或多列)的重复值(只可以查出重复记录的值，不能查出整个记录的信息)
例如:查找stuid，stuname重复的记录

    select stuid，stuname from stuinfo
　　group by stuid，stuname
　　having(count(*))>1

11.查某一列有重复值的记录(此方法查出的是所有重复的记录，如果有两条记录重复的，就查出两条)

　　例如:查找stuid重复的记录

以下是代码片段：
    select * from stuinfo
　　where stuid in (
　　select stuid from stuinfo
　　group by stuid
　　having(count(*))>1
　　)

12. DateADD()方法的使用

--1.一个月第一天的
Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
--2.本周的星期一
Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
--3.一年的第一天
Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
--4.季度的第一天
Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
--5.当天的半夜
Select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
--6.上个月的最后一天
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
--7.去年的最后一天
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
--8.本月的最后一天
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
--9.本年的最后一天
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
--10.本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
--11--去年
select  convert(varchar(4),dateadd(year,-1,getdate()),120)

总结：dateadd(year,-1,getdate()) year /month/day 都可以 -1、0、1数字代表相应。

 13 将某个表中 2008371329 0513 10015 【0513】改成【0139】，运用 substring();
update r_user set studentnumber = substring(studentnumber,1,10)+'0513'+substring(studentnumber,15,5) where substring(studentnumber,11,4)='0139'

14. replace 替换在sql中的使用

select top 18 pro_code,REPLACE(REPLACE(pro_name,'教育厅',''),'教育局','') pro_name from sys_province

15. 使用 except 、Not Exists 、Exists 代替 not in 、in 

 注意 Not Exists、 exists 返回值是 bool 类型

16.

CHAR，NCHAR 定长，速度快，占空间大，需处理
VARCHAR，NVARCHAR，TEXT 不定长，空间小，速度慢，无需处理
NCHAR、NVARCHAR、NTEXT处理Unicode码

空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

17.