【不断更新中】SQL Server 2008常用的数据库知识整理

数据库知识点：

一、计算字段

1、NULLIF函数 将 null 的姓名转化为“佚名”；

    表达式：NULLIF ( expression1 , expression2 )
    含义：如果两个表达式不等价， 则 NULLIF 返回第一个 expression1的值。
         如果两个表达式等价， 则 NULLIF 返回第一个 expression1类型的空值。

2、Beween And 计算字段
   如：Fsalary Between Fage*1.5+2000 And Fage*1.8+5000

3、SQL的语法计算，取代代码的计算
   如： Update t set fgroup=id/10

 

二、组合查询 Union

1、UNION操作符可以将两个（或多个）查询语句的两个查询结果集合并成一个结果集；

2、连接原则：
   一是每个结果集必须有相同的列数； 二是每个结果集的列必须类型相容。

3、默认规则：
   UNION运算符合并了两个查询结果集， 其中完全重复的数据行被合并为了一条

4、UNION ALL获得所有的结果集。

 

三、匹配

1、_(补位)----单字符匹配

2、%（所有）----多字符匹配

3、[]----集合匹配
   如：[AB]%，以“A” 或者“B“开头长度， 长度任意。
   注：集合取反，“^”,如“[^AB]%”

 

四、in语句

1、多值检测
   如：SELECT FAge,FNumber,FName FROM T_Employee
   WHERE FAge IN (23, 25, 28)

2、范围值检测（不提倡，推荐用between...and...）

 

五、WHERE 1=1 使用动态组装的SQL

1、指定一个永远为真的条件，不需要再判断where是否存在了；
2、容易造成性能损失；
3、最好where与and都要拼接。　

六、窗口函数ROW_NUMBER()

1、含义：计算每一行数据在结果集中的行号（从1开始计数）

2、例：SELECT ROW_NUMBER() OVER(ORDER BY FSalary),FNumber,FName,FSalary,FAge
      FROM T_Employee

3、扩展：
   第3行到第5行的数据：
   SELECT * FROM
  (
   SELECT ROW_NUMBER() OVER(ORDER BY FSalary DESC) AS rownum,
   FNumber,FName,FSalary,FAge FROM T_Employee
  ) AS a
  WHERE a.rownum>=3 AND a.rownum<=5

七、数学函数

1、ABS()函数，求绝对值；

2、POWER()函数：
   一般power（sex,2）,第一个参数为待求幂的表达式， 第二个参数为幂。
   如select POWER(2,3) 结果为8。

3、SQRT()函数，计算平方根；

4、RAND()函数，求随机数（select RAND();）

5、舍入到最大整数，CEILING()函数；
   舍入到最小整数， FLOOR()函数；
   四舍五入，ROUND()函数。
6、DATENAME()函数
  可选值    别名       说明
  Year   yy、 yyyy    年份
  Quarter  qq, q      季度
  Month   mm, m       月份
  Dayofyear dy, y  每年的某一日
  Day      dd, d      日期
  Week    wk, ww      星期
　Weekday   dw       工作日
  Hour      hh        小时
  Minute   mi, n      分钟
  Second   ss, s       秒
  Millisecond ms      毫秒
  实例：select f_GUID,DATENAME(Weekday,BirthDate) 星期
       from T_Teacher

7、CAST()和CONVERT()两个函数
  实例：
      select IDnumber,RIGHT(IDnumber,6) 后六位 from T_TeacherTest
      select IDnumber,cast(RIGHT(IDnumber,6) AS INTEGER)+1 as '后六位' 
      from T_Teacher
      select IDnumber,CONVERT(INTEGER,RIGHT(IDnumber,6))+1 as '后六位' 
      from T_Teacher(注意类型)

  8、COALESCE()函数
     功能：空值处理
　　 表达式：COALESCE ( expression,value1,value2……,valuen)
　　 实例：
        select f_name,f_csrq,COALESCE(f_csrq,'2015-10-10') as 重要时期
　　     from T_Student where f_name = '11'
      （若f_csrq为NULL，则重要时期为2015-10-10；否则，重要时期为f_csrq）
       select f_name,f_csrq,f_zz,COALESCE(f_csrq,f_zz,'2015-10-10')
      as 重要时期 from T_Student where f_name = '11'
      （若f_csrq为NULL，则再次判断f_zz，理解同上）
    简化版：ISNULL()函数

    

  

  9、CASE函数
     表达式：
　　       CASE
　　　　　　　　WHEN condition1 THEN returnvalue1
　　　　　　　　WHEN condition 2 THEN returnvalue2
　　　　　　　　WHEN condition 3 THEN returnvalue3
　　　　　　　　……
　　　　　　　　ELSE defaultreturnvalue
　　　　　　END
　　 实例：
　　　　  select Status,(case Status
　　　　　　when '0' then '待审核'
　　　　　　when '1' then '审核中'
　　　　　　when '2' then '审核通过'
　　　　　　else '审核不通过'
　　　　  end
　　　　　　) as 状态
　　　　  from CheckTable

       

  10、PATINDEX()函数
      表达式：PATINDEX ( '%pattern%' , expression )
　　   含义：它返回指定表达式中模式'%pattern%'第一次出现的起始位置；
　　 　　　　如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。
　　　　   （可以使用通配符）
　　　 实例：
　　　　　　select f_name,PATINDEX('%小%',f_name) as 索引位置 from T_Student 

　　　　　　
　　　　　　select f_name,PATINDEX('%_小%',f_name) as 索引位置 from T_Student 

　　　　　　

  

  11、几个相关不太重要的函数
　　 （1）REPLICATE ()函数，用来得到一个子字符串重复了若干次所组成的字符串，
　　　　  表达式：REPLICATE (str,count)；
　　 （2）REVERSE()函数，将一个字符串的顺序颠倒；
 　　（3）ISDATE()函数，用来确定输入表达式是否为有效日期；
　　 （4）ISNUMERIC()函数，用来确定表达式是否为有效的数值类型；

 八、索引与约束

1、索引
     创建：create index 索引名 on 表名（字段）
     删除：drop index 表名.索引名
          drop index 索引名 on 表名（MYSQL用法）
          drop index 索引名（Oracle 和 DB2的用法）
2、约束：非空约束、唯一约束（UNIQUE 约束，如身份证）、CHECK 约束（如age>0）
        主键约束（PRIMARY KEY）、外键约束（）
    　　实例：
           CardID(CardID VARCHAR(18) UNIQUE
           Age INT CHECK(Age >0)

 

 九、表连接

 1、内连接（inner join）
 2、不等值连接
 3、交叉连接
 4、自连接
 5、外部连接
 6、左外部连接（left join）
 7、右外部连接（right join）
 8、全外部连接（full outer join）
注：表连接一般只是【表名】left join 【表名】 on... 
   在这里就不一一展示了，展示8个，只是为了面试时多说几个连接方式，增加面试官的的好感。

十、子查询

1、单值子查询（标量子查询）：查询一个列的一行记录
  （1）、select 字段名,（一个select 的查询语句--只有一个记录）
　　　　 实例：select 1 as 测试, 
　　　　　　　(select f_name from Student where f_NO = '1') as 子查询
　　　　　　　 

              

  （2）、select 字段名 from 表名 where 某个字段 = （一个select查询语句--只有某列某个记录，即某个值）

2、列值子查询（表子查询）：查询一个列的一行记录
   select a.name,b.name from a ,(select * from student s where s.Id < 1000) b
3、集合运算符与子查询（in、any、all、exists）
   (1) in (select 查询语句)
　　　　select * from Student where name in
　　　　(select f_name from StudentTest where name= '王小小')
　　　　

         

   (2)、any与in用法相同，任何的

   (3)、all与in用法相同，所有的

   (3)、exists与in用法相同，除了

 

 十一、数据库单表数据迁移

1、普通的不同数据库的表数据迁移（表结构完全相同）
    insert into [新数据库名称].[dbo].[新数据库表名] 
    select * from [老数据库名称].[dbo].[老数据库表名]

2、数据库表结构不同的方法（例子如下，自己对比总结）
    insert into [新数据库名称].[dbo].[新数据库表名] 
               (audituserName,ID,UnitID,Year,Quarter,FileName,Status,ReportTime,CreateTime)
     (select filename, id,ar,upyear,quarter,REPLACE(SUBSTRING(filename,13,LEN(filename)) ,'.doc',''),tmp_xzstate,jointime,at 
 9    from [老数据库名称].[dbo].[老数据库表名])

   详细地址：http://www.cnblogs.com/eadily-dream/p/5378381.html

 十二、好用的stuff(param1, startIndex, length, param2)

　　　 1、简单的说明：

　　　　　stuff(param1, startIndex, length, param2)
　　　　   将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。

 

　　　　　注：　　　　　

　　　　　　1）param1：一个字符数据表达式。param1可以是常量、变量，也可以是字符列或二进制数据列。

　　　　　　2）startIndex：一个整数值，指定删除和插入的开始位置。如果 startIndex或 length 为负，则返回空字符串。

　　　　　　　  如果startIndex比param1长，则返回空字符串。startIndex可以是 bigint 类型。

　　　　　　3）length：一个整数，指定要删除的字符数。如果 length 比param1长，则最多删除到param1 中的最后一个字符。length 可以是 bigint 类型。

　　　　　　4）param2,返回类型。如果param1是受支持的字符数据类型，则返回字符数据。如果param1是一个受支持的 binary 数据类型，则返回二进制数据。

　　　　2、实例展示：

 　　　　我的实例："重点修改 匹配核查不同的状态结构"

 

  

未完待续。。。

最近网上发现有博客和我的一样，这里注明转载必须标明出自哪里的博客园和作者的原贴链接地址，

特在此禁止新浪等一些博客转载的平台转载，理由本人不喜欢这些平台看到自己的东西，

只容许两个平台cnblogs和csdn

谢谢！