SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY id) AS rowN,FNumber, FName,FSalary ,Fage
FROM dbo. T_Employee) AS a
WHERE a. rowN >=3 AND a. rowN <=5

SELECT distinct CREATEDate,state FROM dbo .TwodimensionalcodeHistory
SELECT * FROM TwodimensionalcodeHistory

SELECT FName, Fage,FSubCompany FROM T_Employee
SELECT 'XXX 子公司' as CompanyName, '30000000' as RegAmount,FName, Fage,FSubCompany FROM T_Employee

--计算某几列的算数和
--FAge * FSalary 并不是一个实际存在的列 ,但是他们在查询出来的数据中看起来是一个实际存在的字段 ,他们
--完全可以看成一个普通字段
SELECT FNumber, FName,FAge * FSalary AS FSalarIndex FROM T_Employee

--计算字符长度函数len(字段 )
SELECT FName, LEN(FName ) FROM T_Employee where FName IS NOT NULL

--截取字符串函数subString(字段 ,截取起始位置,截取长度 )  起始位置从开始
--我们截取从第二个字符开始开始截取 ,长度为位
SELECT FName, SUBSTRING(FName ,2, 3) FROM T_Employee WHERE FName IS NOT NULL


--多个函数嵌套计算正弦函数 ,和绝对值函数
SELECT FName, Fage,SIN (Fage), ABS(SIN (Fage)) FROM T_Employee

--java c# 中通过+ 来连接字符串 ,MS SQL中也可以这样
--如果第一个数字类型,sql会默认转换 +后面的格式为数字类型;如果转换失败则报错
SELECT 12+ '33','12' +'33', Fage+'1' ,FAge FROM T_Employee

CONCAT

--字符串拼接中MySQL最灵活可以使用 '+' 和Concat() 函数拼接;MS SQL中使用 '+'拼接
--Oracle 中使用'||' 双竖杆进行拼接等同于 '+' 也可以使用Concat()函数但是与 My Sql中的函数有所不同,只支持俩个参数
--不支持俩个以上字符串的拼接需要达到 My Sql中的效果, 可以使用多个 Concat()函数嵌套使用;DB2 中也使用 '||'进行拼接
--DB2 中的Concat() 函数不会进行智能转换 ,如果带入数字不会直接转换为字符串 ,直接报错DB2 Concat() 函数也只支持个参数
--不支持俩个以上字符串拼接
--语句拼接
SELECT ' 工号为'+ Fnumber+' 的员工姓名为 :'+Fname from T_Employee

--计算处于合理工资范围内的员工 (我们规定上限为年龄的+5000 和下限年龄的倍 +2000 为合理范围)
SELECT * FROM T_Employee WHERE FSalary BETWEEN Fage*1.5 +2000 AND Fage*1.8+ 5000

--不从实体表中取的数据
--MySql 和MSSql 允许使用不带 From子句的Select 语句来查询这些不属于任何实体表的数据
--在oracle 中不允许使用这种不带 From子句的select. 不过我们可以变通实现 from oracle中的系统表
--select 1,Length('abc') from DUAL
SELECT 1
SELECT LEN ('abc')
SELECT 1, 2,3 ,'a', 'b','c'

--联合结果集
--有时候我们需要组合俩个完全不同的查询结果集 ,而这俩个查询结果之间没有必的联系 ,只是我们需要将他们显示在
--一个结果集中而已在SQL中可以使用 union 运算符来将俩个或者多个查询结果集联合为一个结果集中
--需要列数相同和相同位置的列字段类型相容 (技巧可以通过常量字段补足就好了 select '1',xxx from 表)
SELECT fnumber, fname,Fage FROM dbo. T_Employee
union
SELECT FidCarNumber, FName,Fage FROM  dbo. T_TempEmployee
--默认情况下,union合并了俩个查询结果集 ,其中完全重复的数据行被合并为一条如果需要在联合结果集中返回所有的
--记录而不管他们是否唯一 ,则需要在union 后使用all 比如下面
SELECT fnumber, fname,Fage FROM dbo. T_Employee
UNION ALL
SELECT FidCarNumber, FName,Fage FROM  dbo. T_TempEmployee

--联合结果集在制作报表的时候经常被用到 ,我们可以使用联合结果集将没有直接关系的数据显示到同一张报表中
--被连接的的俩个SQL 语句可以是非常复杂的也可以使非常简单的只要符合 union的俩个规则就好了
--范例员工年龄报表要求查询员工的最低年龄和最高年龄 ,临时工和公式工要分开查询
SELECT ' 正式员工最高年龄 ',max (Fage) from T_Employee
UNION
SELECT ' 正式员工最低年龄 ',min (Fage) from T_Employee
UNION
SELECT ' 临时工员工最高年龄 ',max (Fage) from T_TempEmployee
UNION
SELECT ' 临时员工最低年龄 ',min (Fage) from T_TempEmployee
--正式员工工资报表要求查询每位正式员工的信息 ,工号, 工资并在最后一行加上所有员工工资的合计
select FName, FSalary From T_Employee
UNION
SELECT ' 工资合计', sum(FSalary ) from T_Employee



--建表语句
GO
/****** 对象:  Table [dbo].[T_Person2]    脚本日期: 07/03/2015 13:52:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_Person2] (
      [FIdNumber] [varchar] (20) NULL,
      [FName] [varchar] (20) NULL,
      [FBirthDay] [datetime] NULL,
      [FRegDay] [datetime] NULL,
      [FWeight] [decimal] (10, 2) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


123456789120      Tom   1981 /3/ 22 0:00 :00 1998/ 5/1 0 :00: 00  56.67
123456789121      Jim   1987 /1/ 18 0:00 :00 1999/ 8/21 0 :00: 00 36.17
123456789122      Lily  1987 /11/ 8 0:00 :00 2001/ 9/18 0 :00: 00 40.33
123456789123      Kelly 1982 /7/ 12 0:00 :00 2000/ 3/1 0 :00: 00  46.23
123456789124      Sam   1983 /2/ 16 0:00 :00 1998/ 5/1 0 :00: 00  48.68
123456789125      Kerry 1984 /8/ 7 0:00 :00  1999/ 3/1 0 :00: 00  66.67
123456789126      smith 1980 /1/ 9 0:00 :00  2002/ 9/23 0 :00: 00 51.28
123456789127      BillGates   1972 /7/ 18 0:00 :00 1995/ 6/19 0 :00: 00 60.32
NULL  NULL   NULL   NULL  NULL

--SQL 标准中只规定了个数学函数 ,不过很多主流数据库系统都提供了大量常用的数学函数 ,
--而且几乎所有的数据库都提供了支持
--求绝对值ABS() 返回一个数值的绝对值 ,该函数接受一个参数,这个参数为待求绝对值的表达式
SELECT FWeight- 50,ABS (FWeight), ABS(-5.38 )  FROM dbo.T_Person2

--power()函数是用来计算指数的函数该函数接受俩个参数 ,一个为参数为待求幂的表达式 ,第二参数为幂
SELECT FWeight,POWER( FWeight,-0.5 ),POWER( FWeight,2 ),POWER( FWeight,3 ),POWER( FWeight,4 )  FROM dbo.T_Person2

--sqrt()函数是用来计算求平方根的函数该函数接受一个参数 ,这个参数为待计算平方根的表达式
SELECT FWeight, SQRT(FWeight ) FROM T_Person2

--rand() 随机数函数另外还有带参方法提供随机数种子
SELECT RAND ()

--ceiling()舍入到最大整数 这个函数用来舍掉一个数的小数点的后部分并且向上舍入到邻近的最大整数
--比如舍入为4  2.89被舍入为 3   -3.63 舍入为-3  不是-4 注意向上取整
select FWeight, CEILING(FWeight ),CEILING( FWeight*-1 ) FROM T_Person2

--floor() 和ceiling() 相反向下舍入到邻近的最小整数
--比如舍入为3  2.89舍入为     -3.63 舍入为-4 
select FWeight, FLOOR(FWeight ),FLOOR( FWeight*-1 ) FROM T_Person2

--round()函数用来进行四舍五入提供俩个参数 ,和单一参数的用法
--round(m,d) 其中m 为待进行四舍五入的数值而 d则为计算精度,也就是进行四舍五入时保留的小数位数
--比如进行精度为四舍五入为 3.66;3.32122 进行精度为四舍五入为3.321;
--当d 为时,表示不保留小数位数进行四舍五入 3.663 为4;-2.223 为-2;
--特别值得注意的是d 还可以设置为负数这时表示在整数部分进行四舍五入
--比如进行精度为 得到 ; 233.7 进行精度为得到200;3321.22 进行精度为 得到3300;
--单一参数相当与于把d默认看出就好了 MS SQLSERVER 和DB2 上并不支持单一参数
SELECT FName,FWeight, ROUND(FWeight ,1), ROUND(FWeight *-1, 0),ROUND (FWeight,- 1) FROM T_person2

--这些函数我个人认为用到次数不是很多案例就直接略过 ,举例下用到再补充
--正弦函数sin()
--余弦函数cos()
--求反正弦函数asin()
--求反余弦值函数acos()
--求正切值tan()
--求反正切值atan()
--求余切cot()
-- π值PI() 
SELECT PI ()
--弧度制转换为角度制degrees()
--角度制转换为弧度制radlans()
--求符号sign()  如果大于返回 1;等于返回; 小于返回;
--求整除余数mysql 和 oracl 提供了Mod() 函数;Ms sql 提供了 "%"可以直接使用
select FWeight, FWeight%5 FROM T_person2
--求自然数对数log()
--求以为底的对数log10()
--求幂power(x,y) 用来计算x 的 y 次幂


--计算字符串长度
SELECT FName, LEN(FName ) FROM T_Person2

--字符串转化为小写LOWER()
SELECT FName, LOWER(FName ) FROM T_Person2

--字符串转化为大写upper()
SELECT FName, upper(FName ) FROM T_Person2

--截取字符串左侧空格ltrim()
--截取字符串右侧空格rtrim()
--截取字符串俩侧空格trim() MSSQL中不支持要用上面俩个函数组合实现

--截取字符串substring(String,start_position,length) 跟C# 语法一样
SELECT SUBSTRING ('123456789', 2,3 )

--查询子字符在主字符串是否存在及位置 [charindex](expression1, expression2, [[start_location]])
--expression1 要查询的字符串,子字符 ;expression2主字符串;[start_location] 开始搜寻位置
 SELECT FName,CHARINDEX( FName,'m' ,0), CHARINDEX(FName ,'m'), CHARINDEX('m' ,FName) FROM T_Person2
 
 --从左侧截取子字符串 substring() 类似的函数leef(string,length)
 --从右侧截取字符串   right(String,length)
 
 --字符串替换 replace(string,string_tobe_replace,string_to_replace)
 --string 原字符串 ;string_tobe_replace需要替换的字符串;用什么字符去代替原字符
 SELECT REPLACE('123456789' ,'45', '77')
 
 
 --得到字符的 ASCII码ASCII('char') 注意只能是单个字符如果是字符串的话 ,得到是首字母的ASCII码
 SELECT ASCII('b' ),ASCII( 'bac')
 
 --与 ASCII码相反的函数,根据数字反向获取这个数字的 ASCII码char()
 SELECT CHAR(98 )
 
 --发音匹配度 ,如果做输入法,或者模糊查询之类或许用的到 soundex()
 SELECT SOUNDEX('jack' ),SOUNDEX( 'jeck'),SOUNDEX ('joke'), SOUNDEX('juke' ),SOUNDEX( 'look'),SOUNDEX ('jobe')
 --还有一个关于发音相似度函数 ,因为发音特征值含义非常复杂 ,difference(),俩简化俩个字符串的发音比较返回
 --来反应俩个字符的相似度数字越小越接近是不是用这个结合下拉框选择项是不是很给力
 SELECT FName ,DIFFERENCE( FName,'Kerry' ) FROM T_Person2
 


 --MySql Ms Sqlserver 和 DB2中可以用字符串表示日期时间类型 ,数据库系统会自动在内部将他们转换为日期时间类型
 --取得当前日期函数 GETDATE() 可以看到返回信息包括了日期 ,时间, 精确到秒以后的时间戳信息
 --Ms SQL server并没有专门提供取得当前日期 ,时间的函数,不过我们可以将 GETDATE()的返回值进行处理
 --需要借助 CONVERT()函数
 SELECT GETDATE() AS 当前时间
 SELECT CONVERT(VARCHAR (50), GETDATE(),101 ) AS 当前日期
 SELECT CONVERT(VARCHAR (50), GETDATE(),108 ) AS 当前时间
 
 --MS SQL 提供 DATEADD()函数用于进行日期时间的加法运算
 --dateadd(datepart,numnber,date)
 --date为待计算的日期 ;参数datepart 指定要返回新值的日期的组成部分 ,需要计算的部分;
 --number 计算值
 SELECT DATEADD(day ,3, '2015-7-20 11:20:56')
 
 --计算每个人出生后年 (year),20季度(quarter),68 个月(month)以及周 (week)前的日子
SELECT FBirthDay,DATEADD( YEAR,3 ,FBirthDay), DATEADD(quarter ,20, FBirthDay),
DATEADD(MONTH ,68, FBirthDay),DATEADD (week,- 1000,FBirthDay )
FROM T_Person2

--计算日期差额,比如计算回款日 ,验收日之间的天数,检索最后一次登录日期大于天的用户
--MS SQL 提供了DATEDIFF() 计算指定差额 datediff(datepart,startdate,enddate)
--datepart单位( 参考表格)  startdate开始日期 enddate结束日期
SELECT FRegDay, FBirthDay,DATEDIFF (week, FBirthDay,FRegDay ) FROM T_Person2


--计算一个日期是星期几(比如安排报道日期为周末 ,则需要向后顺延)
--MS SQL 提供了dateName(datepart,date)  datepart 要返回日期参数 ,要返回周就是(week)
--可选参数如图
SELECT FRegDay, DATENAME(dw ,FRegDay), FBirthDay,DATENAME (dw, FBirthDay) FROM T_Person2

--DATENAME 也可以用来取得日期的指定部分诸如月份年份时分秒等
--另一个函数是datepart(datepart,date)
--虽然俩个函数都能提取日期的特定部分 ,但是datepart() 返回值为数值 ,而dateName 函数则会尽可能的
--以名称方式返回返回值
SELECT FRegDay,datepart( dayofyear,FRegDay ),FBirthDay, DATENAME(year ,FBirthDay) FROM T_Person2


--类型转换函数
--系统计算时会自动将字符串转为整数类型 ,这种转换为隐式转换有时候不能自动转换 ,
--我们需要手动显示转换;显示转换不仅可以保证类型转换的正确性 ,而且还可以提供数据
--的处理速度,因此应该尽量使用显示转换 ,避免使用隐式转换
-- MS SQL 提供cast() convert() 俩个类型进行转换 ,
-- cast(expression as data_type) 符合ANSI SQL 99的函数 ;
-- convert(date_type,expression) 是符合ODBC标准的函数
SELECT CAST ('-30' AS INT),CONVERT (DECIMAL, '3.1415726'),CONVERT (DATETIME, '2015-7-21 11:28:24')

--将每个人的身份证后位转换为整数类型并进行相关计算
SELECT FIdNumber,RIGHT( FIdNumber,3 ) AS 后位, cast(RIGHT(FIdNumber ,3) as int) as 后位整数
,cast(RIGHT( FIdNumber,3 ) AS INT)+ 1 AS 后位加
,CAST(RIGHT( FIdNumber,3 ) AS INT)/ 2 AS 后位除以
 FROM T_Person2
 
 
 --空值处理
 --如果名称为空则返回别名 ,或者特定值
 --coalesce(expression,value1,value2,...,valuen)函数
 --判断 expression是否为空, 不为空则正常显示 ,如果为空则用value1代替 ,
 --如果 value1也为空则用,value3代替以此类推
SELECT FBirthDay, FRegDay,COALESCE (FBirthDay, FRegDay,'2008-08-08' ),
COALESCE(FRegDay ,'1990-06-05')
FROM T_Person2

--空值处理的另一个函数isnull(expression,value)算是 coalesce的简化版
SELECT FBirthDay, FRegDay,ISNULL (FBirthDay, FRegDay),
ISNULL(FRegDay ,'1990-06-05')
FROM T_Person2

--nullIf(expression1,expression2)
--如果俩个表达式不等价,则返回第一个 expression1的值, 如果等价,则返回第一个
--expression1类型的空值
SELECT FBirthDay, FRegDay,nullIf (FBirthDay, FRegDay)
FROM T_Person2

--流程控制函数case()
--如果年龄大于返回姓名,否则返回别名
--CASE experession
--WHEN value1 THEN returnValue1
--WHEN value2 THEN returnValue2
--WHEN value3 THEN returnValue3
--ELSE defaulretunValue
--end
--类似于编程中的switch...case
--如果FName 叫Tom则返回 GoodBoy,叫Jim 则返回GoodGril,Lily返回 badBoy,
--Kelly返回badGrill, 其他返回Normal
SELECT FName,( CASE FName
WHEN 'Tom' THEN 'GoodBoy'
WHEN 'Jim' THEN 'GoodGril'
WHEN 'Lily' THEN 'badBoy'
WHEN 'Kelly' THEN 'badGrill'
ELSE 'Normal'
END) FROM T_Person2

--用法
--上面的case 语句只能用于相等情况下的 ,如果要判断,年龄小于则返回未成年
--否则返回成年,就很乏力需要用到 case()另一种用法
--CASE
--WHEN condition1 THEN returnValue1
--WHEN condition2 THEN returnValue2
--WHEN condition3 THEN returnValue3
--....
--ELSE defaultReturnValue
--END
--其中condition1,condition2,condition3 为条件表达式 ,如果condition1 为真,
--则返回returnValue1; 否则执行条件 ,如果condition2 为真则返回 returnValue2,
--否则执行条件condition3 不符合上面的条件则返回默认值 defaultReturnValue

--来判断一个人体重如果小于则为太瘦
--来判断一个人体重如果大于则认为太胖
--40~50为正常
SELECT FName, FWeight,(CASE WHEN FWeight <40 THEN 'thin' WHEN FWeight> 50
THEN 'fat' ELSE 'OK' END )
 FROM T_Person2
 
 --MS SQL 独有函数
 --patindex('%pattern',expression) 值匹配, 获取指定字符 ,在目标字符中的位置;
 --更官方的说明 :用来计算字符串中指定表达式的开始位置 ,确定值匹配
--查找姓名中包含m 出现的位置
 SELECT FName ,PATINDEX( '%_m%',FName ) FROM T_Person2
 
--获取字符串重复N次后的字符串 replicate(str,count) 字面意思复制
select Fname, replicate(FName ,2) FROM T_Person2
--字符串颠倒reverse()
SELECT FName, REVERSE(Fname ) FROM T_Person2

--isDate(expression) 用来确定输入的表达式是否为有效日期判断日期的合法的函数
--如果判断是日期则返回不是日期格式则返回
SELECT ISDATE ('ZZZZZ'), ISDATE('2015-7-21 16:17:54' ),ISDATE( '2015080A'),
ISDATE('20150606' )

--isnumeric(expression) 函数用来确定表达式是否为有效的数值类型
--如果输入的表达式为有效整数 ,浮点数money 或者decimal类型时 ,返回, 否则返回
SELECT ISNUMERIC('str' ),ISNUMERIC(NULL), ISNUMERIC('0.234' ),ISNUMERIC( '-30')

--辅助功能函数
--app_name()函数返回当前会话的应用程序名称 ;
--current_user  注意这个函数不带括号调用返回当前用户的登录名
--host_name() 返回工作站名
SELECT APP_NAME (),CURRENT_USER, HOST_NAME()

--生成全局唯一字符串函数 newId()
SELECT NEWID (),NEWID()



--利用SQL 语句创建索引
--CREATE INDEX 索引名称on 表名(字段 ,字段, 字段字段n)
--索引名称必须为唯一的,字段 ,字段, 允许一个到多个
--范例为T_person 表中给FName创建索引索引名为 idx_person_name
CREATE INDEX idx_person_name ON T_Person (FName)

--删除索引
--drop index 表名索引名
DROP INDEX T_person.idx_person_name

 --非空约束
 --在定义数据库的时候 ,默认情况下所有字段都是允许为空值的 ,
 --如果需要在创建表的时候显示指定禁止一个字段为空的方式就是
 --在字段定义后增加 not null, 范例如下
 --CREATE TABLE T_notNull(Fnumber VARCHAR(20) NOT NULL,FName VARCHAR(20),FAge INT)

--唯一约束
--唯一约束又称为unique约束 ,它用于防止一个特定的列中俩个记录具有同样的值
--设置方式就是在字段定义后增加 unique
--CREATE TABLE T_UniqueTest(Fnumber VARCHAR(20) UNIQUE,FName VARCHAR(20),Fage INT)

--check约束
--check约束会检查输入到记录中的值是否满足一个条件 ,如果不满足这个条件则
--对数据库的修改不会成功
--比如一个人年龄不可能是负数 ,一个人的入学日期不可能早于出身日期 ,出厂月份
--不可能大于月可以在check条件中使用任意有效的 SQL表达式,check 约束对于插入 ,
--更新等任意对数据修改的操作都进行检查
--具体需求的check约束 ,根据需求百度,我开发至今都不怎么去使用 check,一方面客户
--不停的变更会不停的挑战 check,导致修改频繁,不实用测试数据插入变得麻烦
--check约束范例
CREATE TABLE CHECKTABLE(Fid INT,Fname VARCHAR(20 ),
Fage VARCHAR( 20) CHECK(Fage >0),
FWorkYear INT CHECK ( FWorkYear>0 ))

--主键约束
--由于每张表都要有主键,因此主键约束是非常重要的 ,而且主键约束是外键关联的基础
--主键约束为表之间的关联提供了链接点
--主键必须能够唯一标识一条记录 ,也就是主键字段中的值必须是唯一的 ,而且不能包含
--NULL值从这种意义来说,主键约束是 unique约束和非空约束的组合虽然一张表中可以
--有多个unique 约束和非空约束但是每个表却只能有一个主键约束
--字段后面增加primary key
--主键约束范例:
CREATE TABLE PrimaryTable(Fid INT PRIMARY KEY, Fname V