SELECT * FROM (SELECT ROW_NUMBER() OVER ( ORDER BY id) AS rowN,FNumber, FName,FSalary ,FageFROM dbo. T_Employee) AS aWHERE a. rowN >=3 AND a. rowN <=5SELECT distinct CREATEDate,state FROM dbo .TwodimensionalcodeHistorySELECT * FROM TwodimensionalcodeHistorySELECT FName, Fage,FSubCompany FROM T_EmployeeSELECT '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_EmployeeCONCAT--字符串拼接中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 DUALSELECT 1SELECT LEN ('abc')SELECT 1, 2,3 ,'a', 'b','c'--联合结果集--有时候我们需要组合俩个完全不同的查询结果集 ,而这俩个查询结果之间没有必的联系 ,只是我们需要将他们显示在--一个结果集中而已在SQL中可以使用 union 运算符来将俩个或者多个查询结果集联合为一个结果集中--需要列数相同和相同位置的列字段类型相容 (技巧可以通过常量字段补足就好了 select '1',xxx from 表)SELECT fnumber, fname,Fage FROM dbo. T_EmployeeunionSELECT FidCarNumber, FName,Fage FROM dbo. T_TempEmployee--默认情况下,union合并了俩个查询结果集 ,其中完全重复的数据行被合并为一条如果需要在联合结果集中返回所有的--记录而不管他们是否唯一 ,则需要在union 后使用all 比如下面SELECT fnumber, fname,Fage FROM dbo. T_EmployeeUNION ALLSELECT FidCarNumber, FName,Fage FROM dbo. T_TempEmployee--联合结果集在制作报表的时候经常被用到 ,我们可以使用联合结果集将没有直接关系的数据显示到同一张报表中--被连接的的俩个SQL 语句可以是非常复杂的也可以使非常简单的只要符合 union的俩个规则就好了--范例员工年龄报表要求查询员工的最低年龄和最高年龄 ,临时工和公式工要分开查询SELECT ' 正式员工最高年龄 ',max (Fage) from T_EmployeeUNIONSELECT ' 正式员工最低年龄 ',min (Fage) from T_EmployeeUNIONSELECT ' 临时工员工最高年龄 ',max (Fage) from T_TempEmployeeUNIONSELECT ' 临时员工最低年龄 ',min (Fage) from T_TempEmployee--正式员工工资报表要求查询每位正式员工的信息 ,工号, 工资并在最后一行加上所有员工工资的合计select FName, FSalary From T_EmployeeUNIONSELECT ' 工资合计', sum(FSalary ) from T_Employee--建表语句GO/****** 对象: Table [dbo].[T_Person2] 脚本日期: 07/03/2015 13:52:39 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFF123456789120 Tom 1981 /3/ 22 0:00 :00 1998/ 5/1 0 :00: 00 56.67123456789121 Jim 1987 /1/ 18 0:00 :00 1999/ 8/21 0 :00: 00 36.17123456789122 Lily 1987 /11/ 8 0:00 :00 2001/ 9/18 0 :00: 00 40.33123456789123 Kelly 1982 /7/ 12 0:00 :00 2000/ 3/1 0 :00: 00 46.23123456789124 Sam 1983 /2/ 16 0:00 :00 1998/ 5/1 0 :00: 00 48.68123456789125 Kerry 1984 /8/ 7 0:00 :00 1999/ 3/1 0 :00: 00 66.67123456789126 smith 1980 /1/ 9 0:00 :00 2002/ 9/23 0 :00: 00 51.28123456789127 BillGates 1972 /7/ 18 0:00 :00 1995/ 6/19 0 :00: 00 60.32NULL 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, 其他返回NormalSELECT FName,( CASE FNameWHEN '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> 50THEN '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_nameCREATE 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 VARCHAR( 20))--外键约束--当一些信息在表中重复出现的时候 ,我们就要考虑将他们提取到另外一张表中 ,--然后在源表中引用新创建的表中的数据比如很多作者都有不止一本著作 ,所以--在保存书籍信息的时候,应该把作者信息放到单独的一张表 (然后把作者ID放到书籍表中 )--范例格式: foreign key 外键字段references 外键表名 (外键表的主键字段)--比如下面的SQL语句就是添加了外键约束 T_Author 表和T_Book 表的创建语句CREATE TABLE T_AUTHOR(Fid VARCHAR(20 ) PRIMARY KEY, FName VARCHAR( 100),Fage INT ,FEmail VARCHAR (20));CREATE TABLE T_BOOK(Fid VARCHAR(20 ) PRIMARY KEY, FName VARCHAR( 20),FPageCount INT ,FAuthorId VARCHAR (20),FOREIGN KEY ( FAuthorId) REFERENCES T_AUTHOR( Fid))--表连接--建表及测试数据--T_Customer 客户信息表--T_OrderType 订单类型--T_Order 订单信息CREATE TABLE T_Customer(Fid INT NOT NULL,FName VARCHAR(20 ) NOT NULL,Fage INT, PRIMARY KEY (Fid))CREATE TABLE T_Order(Fid INT NOT NULL,FNumber VARCHAR(20 ) NOT NULL,FPrice NUMERIC (10, 2),FCustomerId INT,FTypeId INT,PRIMARY KEY( Fid))CREATE TABLE T_OrderType(Fid INT NOT NULL,FName VARCHAR(20 ) NOT NULL,PRIMARY KEY(Fid ))INSERT INTO T_Customer(Fid ,FName, Fage)VALUES(1 ,'Tom', 21)INSERT INTO T_Customer(Fid ,FName, Fage)VALUES(2 ,'MIKE', 24)INSERT INTO T_Customer(Fid ,FName, Fage)VALUES(3 ,'JACK', 30)INSERT INTO T_Customer(Fid ,FName, Fage)VALUES(4 ,'Tom', 25)INSERT INTO T_Customer(Fid ,FName, Fage)VALUES(5 ,'LINDA',NULL)INSERT INTO T_OrderType(Fid ,FName)VALUES(1 ,'MarketCrder')INSERT INTO T_OrderType(Fid ,FName)VALUES(2 ,'LimitOrder')INSERT INTO T_OrderType(Fid ,FName)VALUES(3 ,'Stop Order')INSERT INTO T_OrderType(Fid ,FName)VALUES(4 ,'StopLimit Order')INSERT INTO T_Order(Fid ,FNumber, FPrice,FCustomerId ,FTypeId)VALUES(1 ,'K001', 100,1 ,1)INSERT INTO T_Order(Fid ,FNumber, FPrice,FCustomerId ,FTypeId)VALUES(2 ,'K002', 200,1 ,1)INSERT INTO T_Order(Fid ,FNumber, FPrice,FCustomerId ,FTypeId)VALUES(3 ,'T003', 300,1 ,1)INSERT INTO T_Order(Fid ,FNumber, FPrice,FCustomerId ,FTypeId)VALUES(4 ,'N002', 100,2 ,2)INSERT INTO T_Order(Fid ,FNumber, FPrice,FCustomerId ,FTypeId)VALUES(5 ,'N003', 500,3 ,4)INSERT INTO T_Order(Fid ,FNumber, FPrice,FCustomerId ,FTypeId)VALUES(6 ,'T001', 300,4 ,3)INSERT INTO T_Order(Fid ,FNumber, FPrice,FCustomerId ,FTypeId)VALUES(7 ,'T002', 100,NULL,1 )--JOIN关键字将一个或者多个表按照彼此间的关系连接为一个结果集--inner join 内连接组合俩张表,并且基于俩张表的关联来联系他们--使用内连接需要指表中哪些字段成关联关系 ,并且需要指定基于什么--条件进行连接内连接语法 inner join table_name on condition--大多数系统中,inner join中的 inner是可选的,inner join 是默认的链接方式SELECT * FROM T_OrderSELECT * FROM T_Customerselect * FROM T_Order INNER JOIN T_CustomerON T_Customer. Fid = T_Order .Fid--在使用表连接的时候,可以不局限于只连俩张表 ,因为在很情况下需要--连接许多表SELECT * FROM T_OrderSELECT o. Fid, o .FNumber, o.FPrice ,c. FName, ot .FNamefrom T_Order AS o INNER JOIN T_Customer AS cON o. FCustomerId = c .FidINNER JOIN T_OrderType AS otON o. FTypeId = ot .Fid--不等值连接--上面演示的在on后面设置某字段必须等于另一个表中的字段--除了等值连接,还存在一种不等值连接也就是在连接的条件中可以使用--小于(<) 、大于(>)、不等于 (<>)等于运算, 而且还可以使用 like--,between and等甚至还可以使用函数SELECT o. Fid, o .FNumber, o.FPrice ,c. FNamefrom T_Order AS o INNER JOIN T_Customer AS cON o. FPrice < c .Fage * 5--在可以在等值连接后面添加 and 来添加限制AND o. FCustomerId = c .Fid--交叉连接--与内连接比起来,交叉连接非常简单 ,因为它不存ON子句--交叉连接会将涉及到的所有记录都包含在结果集中可以采用俩种方式--来定义交叉连接,分别是隐私连接和显示的连接SELECT * FROM T_Order --7条数据SELECT * FROM T_Customer --5条数据SELECT * FROM T_Customer ,T_Order--35 条数据= 7*5SELECT * FROM T_CustomerCROSS JOIN T_Order --显示连接 ;隐式连接默认忽略CROSS JOIN--自连接--上面讲的都是在不同数据表之间进行的 ,其实参与连接的表完全可以是--同一张表, 也就是表与其自身的连接 ,这样的连接就被称为自连接SELECT t1.*, t2.* FROM T_Customer t1 INNER JOINT_Customer AS t2 ON t2. Fid = t1 .Fid--外部连接--左外部连接left outer join 右外部连接 right outer join--全外部连接full outer join--外部连接的语法和内部连接几乎一样 ,主要区别就是对于空值的处理--外部连接不需要俩个表具有匹配记录 ,这样可以指定某个表中的记录总是放--到结果集中--左外部连接以左表为基准去匹配数据 ,将左表的数据放到结果集中 ,--无论是否在右表中存在匹配记录--,能匹配到则显示,不能匹配则显示为 NULLSELECT * FROM T_Order --7条数据SELECT * FROM T_Customer --5条数据select o. FNumber, o .FPrice, o.FCustomerId , c. FName, c .Fage FROM T_Order AS o LEFT OUTER JOIN T_Customer AS cON o. FCustomerId =c .Fid--右外部连接以右表为基准去匹配数据 ,将右表的数据放到结果集中 ,--无论是否在左表中存在匹配记录--,能匹配到则显示,不能匹配则显示为 NULLSELECT * FROM T_Order --7条数据SELECT * FROM T_Customer --5条数据select o. FNumber, o .FPrice, o.FCustomerId , c. FName, c .Fage FROM T_Order AS o RIGHT OUTER JOIN T_Customer AS cON o. FCustomerId =c .Fid--全外部连接--几乎所有的数据库都支持左外部连接和右外部连接 ,但是全外部连接--则不是所有数据库都支持的 ,诸如MYsql.--全外部连接是左外部连接和右外部连接的合集 ,因为即使在右表中不存--在匹配连接条件的数据,左表中的所有记录也将被放到结果集中 ,同样--左表中不存在匹配记录,右表中的所有记录也将被放到结果集中select o. FNumber, o .FPrice, o.FCustomerId , c. FName, c .Fage FROM T_Order AS o Full OUTER JOIN T_Customer AS cON o. FCustomerId =c .Fid--子查询--SQL允许将一个查询语句作为一个结果集供其他 SQL语句使用, 就像使用--普通的表一样,被当做结果集的查询语句被称为子查询--所有可以使用表的地方都可以使用子查询 ,比如select * from T--上面的T 就可以用子查询来代替 select * from (select * from T2 where--age >= 30) 这里(select * from T2 where age >= 30) 就是子查询--可以将子查询看做为一张临时表 ,这张表在查询开始的时候被创建 ,在查询结束--的时候被销毁子查询大大简化了复杂的 SQL 语句编程--建表及测试数据--T_Reader 读者信息FYearOfBirth 读者出身年份 FProvince读者省份--FYearOfJoin 读者入会年份CREATE TABLE T_Reader(Fid INT NOT NULL,FName VARCHAR(50 ),FYearOfBirth INT, FCity VARCHAR( 50),FProvince VARCHAR(50 ),FYearOfJoin INT)--书籍信息FYearPublished 初版年份FCategoryId所属分类CREATE TABLE T_Book(Fid INT NOT NULL,FName VARCHAR(50 ),FYearPublished INT, FCategoryId INT)--分类信息CREATE TABLE T_Category(FId INT NOT NULL,FName VARCHAR(50 ))--T_ReaderFavorite 读者和类别的对应关系 FReaderId读者主键--FCategoryId分类主键CREATE TABLE T_ReaderFavorite(FCategoryId INT,FReaderId INT)--测试数据INSERT INTO T_Category(FId ,FName) VALUES(1 ,'Story') --故事INSERT INTO T_Category(FId ,FName) VALUES(2 ,'History') --历史INSERT INTO T_Category(FId ,FName) VALUES(3 ,'Theory') --理论INSERT INTO T_Category(FId ,FName) VALUES(4 ,'Technology') --技术INSERT INTO T_Category(FId ,FName) VALUES(5 ,'Art') --艺术INSERT INTO T_Category(FId ,FName) VALUES(6 ,'Philosophy') --哲学 --INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(1 ,'Tom', 1979,'TangShan' ,'Hebei', 2003)INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(2 ,'Sam', 1981,'LangFang' ,'Hebei', 2001) INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(3 ,'Jerry', 1966,'DongGuan' ,'DongGuan', 1995)INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(4 ,'Lily', 1972,'JiaXing' ,'ZheJiang', 2005) INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(5 ,'Marry', 1985,'BeiJing' ,'BeiJing', 1999) INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(6 ,'Kelly', 1977,'ZhuZhou' ,'HuNan', 1995) INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(7 ,'Tim', 1982,'YangZhou' ,'HuNan', 2001) INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(8 ,'King', 1979,'JiNan' ,'ShanDong', 1997) INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(9 ,'John', 1979,'QingDao' ,'ShanDong', 2003) INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(10 ,'Lucy', 1978,'LuoYany' ,'HeNan', 1996) INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(11 ,'July', 1983,'ZhuMaDian' ,'HeNan', 1999) INSERT INTO T_Reader(Fid ,FName, FYearOfBirth,FCity ,FProvince, FYearOfJoin)VALUES(12 ,'Fige', 1981,'JinCheng' ,'ShanXi', 2003) INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(1 ,'About J2EE', 2005,4 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(2 ,'Learning Hibernate', 2003,4 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(3 ,'Two Cites', 1999,1 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(4 ,'Jane Eyre', 2001,1 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(5 ,'Oliver Twist', 2002,1 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(6 ,'HisTory of china', 1982,2 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(7 ,'HisTory of England', 1860,2 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(8 ,'HisTory of America', 1700,2 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(9 ,'HisTory of the World', 2008,2 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(10 ,'Atom', 1930,3 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(11 ,'RELATIVITY', 1945,3 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(12 ,'Computer', 1970,3 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(13 ,'Astronomy', 1971,3 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(14 ,'How To Singing', 1771,5 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(15 ,'DaoDeJing', 2001,6 )INSERT INTO T_Book(Fid , FName, FYearPublished, FCategoryId )VALUES(16 ,'ObediencetoAuthority', 1995,6 )INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(1 ,1)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(5 ,2)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(2 ,3)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(3 ,4)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(5 ,5)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(1 ,6)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(1 ,7)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(4 ,8)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(6 ,9)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(5 ,10)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(2 ,11)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(2 ,12)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(1 ,12)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(3 ,1)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(1 ,3)INSERT INTO T_ReaderFavorite(FCategoryId , FReaderId)VALUES(4 ,4)--子查询有两种类型,一种是只返回一个单值的子查询 ,这时它可以--用在单值可以使用的地方 ,可以把它看成一个拥有返回值的函数--另外一种是返回一列值的子查询 ,这时的子查询可以看成在内存--中的临时表--单值子查询--单值子查询的限制,返回值必须只有一行记录 ,而且只能有一个列,--这样的子查询又被称为标量子查询标量子查询可以在 select语句--表达式中, 以及where语句中等很多场合--范例select 语句列表中使用 ,可以把它看成函数就易懂了SELECT 1 AS f1,2 ,(SELECT MIN(FYearPublished ) FROM T_Book),(SELECT MAX(FYearPublished ) FROM T_BOOK ) AS f4--范例列值子查询--PS:如果在子查询中使用别名 ,在引用它的时候,也用别名SELECT T_Reader. FName,t2 .FYearPublished, t2.FNameFROM T_Reader,( SELECT * FROM T_Book WHERE FYearPublished < 1800 ) t2--单值子查询扩展进阶--这个子查询,可以 SELECT MAX(FYearPublished) FROM T_BOOK 选中直接执行select FId, FName,(SELECT MAX( FYearPublished) FROM T_BOOK )FROM T_Category--升级下--这个子查询无法选中单独执行 ,上个子查询没有依赖外部查询字段 ,所以可以--单独选中执行--从而能查询到一类图书最新出版的年份select FId, FName,(SELECT MAX(FYearPublished ) FROM T_BOOK WHERE T_BOOK .FCategoryId = T_Category.FId )FROM T_Category--where 子句中的标量查询--标量子查询不仅可以在select语句的列表中 ,还可以用在where子句中 ,而且--实际应用的子查询很多时候都是用在 where子句中的select FReaderId from dbo.T_ReaderFavoriteWHERE FCategoryId =(SELECT FId from T_Category WHERE FName = 'Story')--首先建立内连接--可以查看俩表的交集--然后根据年份条件筛选利用子查询查找最早的初版年份select c. FId,b .FName, b.FYearPublishedFROM T_Book AS b INNER JOIN T_Category AS cON b. FCategoryId = c .FIdWHERE b. FYearPublished =(SELECT MIN(FYearPublished ) FROM T_Book WHERE T_Book .FCategoryId = c.FId)--集合运算符in,any,all,exists 与子查询的结合--如果子查询是多行多列,这样的子查询可以作为临时表--如果子查询是多行当列,这样的子查询其实是一个结果集--SQL 对这样的结果集提供了in,any,all exeists 等操作符--需求: 检索所有图书出版年份内入会的读者信息SELECT * FROM T_ReaderWHERE FYearOfJoin IN(select FYearPublished FROM T_BOOK)--SQL 中any 和some使用和功能都是相同的和 in运算符不同--any必须和其他比较运算符共同使用 ,而且比较将比较运算符放--在any 关键字符前 ,所比较的值也需求匹配子查询中的任意值SELECT * FROM T_ReaderWHERE FYearOfJoin =any(select FYearPublished FROM T_BOOK)--这个查询结果和上面in的查询结果一样的--也就是说'=any' 等价于In 运算符--而'<>any' 等价于not in 运算符--但是像'<any' 就没法等价了还有其他更详细的需要看--TSQL 技术解密可以更深入--查找任何一个会员出生前初版的图书SELECT * FROM T_ReaderWHERE FYearOfJoin <any(select FYearPublished FROM T_BOOK)--ALL运算符--ALL 运算符要求比较的值需要匹配子查询中的所有值--ALL 运算符同样不能单独使用 ,必须和比较运算符共同使用--下面的SQL 语句用来检索在所有会员入会之前出版的图书 :SELECT * FROM T_BookWHERE FYearPublished <ALL(SELECT FYearOfJoin FROM T_Reader)--上面那个不是等同于可以用 min函数取最小值SELECT * FROM T_BookWHERE FYearPublished <( SELECT min (FYearOfJoin) FROM T_Reader )--ALL运算符同样不能与固定的集合匹配 ,比如下面的SQL是错误的SELECT * FROM T_Book WHERE FYearPublished <ALL( 2001,2002 ,2003)--不过这个限制并不会妨碍功能的实现 ,因为没有必要对固定的集合进行--ALL匹配由于带匹配的集合固定完全可以由其他方式实现诸如SELECT * FROM T_Book WHERE FYearPublished < 2001--当使用ALL 运算符的时候 ,如果待匹配的集合为空,也就是子查询没有--返回任何数据的时候,不论与什么比较符搭配使用 ALL返回的值永远是--true. 如下这个查询语句FProvince='没有省份 ' 查询出来是没有匹配--数据的, 但是结果上面的思维 ,会把T_Book 所有数据查询出来 ;--按照正常的思维返回的结果应该是空才对 ALL运算符的语义就是这样--使用的时候格外注意SELECT * FROM T_BookWHERE FYearPublished <ALL(SELECT FYearOfJoin FROM T_Reader WHERE FProvince ='没有省份 ') --exists 运算符 --和 in ,any,all 运算符不同exists运算符是单目运算符 ,它不与列匹配 --因此它也不要求待匹配的集合是单列的 exists运算符用来检查每一行 --是否匹配子查询 ,可以认为exists 就是用来测试子查询的结果是否为空的 --,如果结果集为空则匹配结果为 false,否则匹配结果为true --EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不 --返回任何数据，而是返回值 True或False --EXISTS 指定一个子查询，检测行的存在。 --下面三个结果都相同 SELECT * FROM T_BOOKWHERE EXISTS(SELECT FName FROM T_Reader WHERE FProvince = 'ShanXi' ) SELECT * FROM T_BOOKWHERE EXISTS(SELECT Null) SELECT * FROM T_BOOK --从前面几个例子来看,使用 exists运算符要么是匹配返回表中的所有--数据, 要么就是不匹配不返回任何数据 ,好像exists 运算符并没有太--大意义其实上面的例子在实际中并不实用 ,exists要和相关子查询--一起使用才有意义在相关子查询中引用外部查询中的这个字段 ,这样--匹配外部子查询中的每行数据的时候 ,相关子查询就会根据当前行的--信息来进行匹配判断,这样就可以实现非常丰富的功能呢 --测试下和in 的区别SELECT * FROM T_BOOKWHERE FCategoryId IN(SELECT Fid from T_Category WHERE FName = 'History')SELECT * FROM T_BOOKWHERE EXISTS(SELECT Fid from T_Category WHERE FName = 'History'AND Fid = T_BOOK.FCategoryId )--其他类型SQL 语句中的子查询--子查询在insert 中的应用--将查询出来的结果批量插入--语法字段需要一一对应--insert into T_ReaderFavorite2(FCategoryId,FReaderId)--SELECT FCategoryId,FReaderId FROM T_ReaderFavorite2--还有其它附加条件where 计算等都可以只需要对应字段类型--子查询在update 语句中的应用--范例UPDATE T_BookSET FYearPublished =(SELECT MAX(FYearPublished ) FROM T_Book)--范例--所有同类书超过本的图书初版日期改为--UPDATE T_Book b1--SET FYearPublished = 2005--WHERE (SELECT COUNT(*) FROM T_Book2 b2-- WHERE b1.FCategoryId=b2.FCategoryId)>3--子查询在delete 语句中的应用--删除同类书超过本delete T_Book b1WHERE ( SELECT COUNT (*) FROM T_Book2 b2 WHERE b1. FCategoryId=b2 .FCategoryId)> 3---开窗函数--测试数据及表USE [NB]GO/****** 对象: Table [dbo].[T_Person2] 脚本日期: 08/14/2015 11:24:46 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[T_Person3] ( [FName] [varchar] (20) NULL, [FCity] [varchar] (20) NULL, [FAge] INT, FSalary INT) ON [PRIMARY]GOSET ANSI_PADDING OFFINSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Tom' ,'BeiJing', 20,3000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Tim' ,'ChengDu', 21,4000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Jim' ,'BeiJing', 22,3500 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Lily' ,'LonDon', 21,2000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('John' ,'NewYork', 22,1000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('YaoMing' ,'BeiJing', 20,3000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Swing' ,'LonDon', 22,2000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Guo' ,'NewYork', 20,2800 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('YuQian' ,'BeiJing', 24,8000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Ketty' ,'London', 25,8500 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Merry' ,'BeiJing', 23,3500 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Smith' ,'ChengDu', 30,3000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Bill' ,'BeiJing', 25,2000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Jerry' ,'NewYory', 24,3300 )SELECT * FROM T_Person3--与聚合函数一样,开窗函数也是对行集组进行聚合计算--但是它不像普通聚合函数那样每组只返回一个值 ,开窗函数可以--为每组返回多个值,因为开窗函数所执行聚合计算的行集组是窗--口ISO SQL 规定了这样的函数为开窗函数 ,在oracle 中则被称为--分析函数, 而在DB2中则被称为 OLAP函数SELECT COUNT (*) FROM T_Person3--下面俩个表达是一个意思SELECT FCity, FAge,COUNT (*) OVER () FROM T_Person3SELECT FCity, FAge,COUNT (*) FROM T_Person3WHERE FSalary < 5000GROUP BY FCity,FAge--OVER关键字表示把函数当成开窗函数而不是聚合函数--SQL标准允许将所有聚合函数用做开窗函数 ,使用OVER 关键字来--区分这俩种用法--上面中count(*)over() 对于查询结果的每一行都返回所有符合--条件的行的条数OVER()关键字后的括号中还经常添加选项 ,用以--改变进行聚合运算的窗口范围如果 OVER()关键字后面的括号中的--选项为空, 则开窗函数会对结果集中的所有行进行聚合运算--partition by 子句--开窗函数over() 关键字后的括号中 ,可以使用partition by 子句--来定义行的区分,从而进行聚合计算与 group by子句不同,--partition by子句创建的分区是独立于结果集的 ,创建的分区只是--提供聚合计算的而且不同的开窗函数所创建的分区也互不影响--范例下面SQL 用于显示每一个人员的信息及所属城市的人员数SELECT FName, FCity, FAge ,FSalary,COUNT(*) OVER ( PARTITION BY FCity)FROM T_Person3--同一个SQL 语句中可以使用多个开窗函数 ,而且这些开窗函数并不会--相互干扰--范例SELECT FName, FCity, FAge , FSalary,COUNT(*) OVER ( PARTITION BY FCity),COUNT(*) OVER ( PARTITION BY FAge) FROM t_Person3--MS SQL 并不支持order by 子句的开窗函数 ;--with子句与子查询--一次定义多次使用用于提取子查询WITHSSSS AS( SELECT FAge FROM T_person3 WHERE FAge <24)SELECT * FROM T_person3 AS t WHERE T.FAge IN ( SELECT * FROM SSSS )--374withcr as ( select FAge from T_person3 ) select * from T_person3 where FAge in ( select * from cr ) 版权声明：本文为博主原创文章，未经博主允许不得转载。