MSSQL数据库各种语句学习资料.doc

精品数据库（catalog）表（table）列（column） 或叫 字段（field） 数据类型（datatype）记录（record） 或叫 行（row）主键（PrimaryKey）索引（index）表关联：这种将两张表通过字段关联起来的方式就被称为“表关联”，关联到其他表主键的字段被称为“外键”例子:select * from employees where age20 or FRemark=Mars*数据检索select * from T_Employeeselect FNumber,FName,FAge,FSalary from T_Employeeselect FNumber as 编号，FName as 姓名，FAge as 年龄 from T_Employee (其中的as不是必须的，是可以省略的)select * from T_Employeewhere FSalary25;几种聚合函数：MAX 计算字段最大值MIN 计算字段最小值AVG 计算字段平均值SUM 计算字段合计值COUNT 统计数据条数select MAX(FSalary) from T_Employeewhere FAge25 注：查询年龄大于25岁的员工的最高工资。select MAX(FSalary) as MAX_SALARY from T_Employeewhere FAge25 select AVG(FAge) from T_Employeewhere FSalary3800 注：统计工资大于3800元的员工的平均年龄。select SUM(FSalary) from T_Employee; 注：统计应支出工资的总额。select MIN(FSalary),MAX(FSalary) from T_Employee; 注：多次使用聚合函数，统计公司的最低工资和最高工资。select COUNT(*),COUNT(FNumber) from T_Employee; 注：COUNT(*)统计的是结果集的总条数，而COUNT(FNumber)统计的则是除了结果集中FNumber字段不为空值（也就是不等于NULL）的记录的总条数。*排序select * from T_Employeeorder by FAge ASC 注：按升序排列， ASC是可以省略的 select * from T_Employeeorder by FAge DESC 注：按降序排列，select * from T_Employeeorder by FAge DESC, FSalary DESC; 注：order by 允许指定多个排序列，首先按第一个排序，分不出的按第二个排序。* select * from T_Employee where FAge23 order by FAge DESC,FSalary DESC;注：ORDER BY 子句要放到where子句后，不能颠倒它们的顺序。*通配符过滤 SQL中的通配符过滤使用LIKE关键字。注：使用通配符时，数据库要对全表进行扫描，所以速度非常慢，不要过分使用通配符。1.单字符匹配 select * from T_Employee where FName LIKE _erry; 注：以任意字符开头，剩余部分为“erry”。 select * from T_Employee where FName LIKE _n_ ; 注：检索长度为4，第三个字符为“n”，其他字符为任意字符的姓名。2.多字符匹配 select * from T_Employeewhere FName LIKE T% ; 注：检索以“T”开头，长度任意， select * from T_Employeewhere FName LIKE %n% ; 注：检索姓名中包含字母“n”的员工信息select * from T_Employeewhere FName LIKE %n_ ; 注：检索最后一个字符为任意字符，倒数第二个字符为“n”长度任意的字符串。select * from T_Employeewhere FName LIKE SJ% ;注：检索的是以“S”或者“J”开头，长度任意的数据select * from T_Employeewhere FName LIKE SJ% ;注： 否定符“”是来对集合取反，即检索的是不以“S”或者“J”开头，长度任意的数据*空值检测 select * from T_Employeewhere FName IS NULL ; 注：不能使用普通的等于运算符进行判断，而要使用IS NULL关键字。select * from T_Employeewhere FName IS NOT NULL ; 注：检索FName字段不为空的数据。select * from T_Employeewhere FName IS NOT NULL AND FSalary5000; 注：查询所有姓名已知 且工资小于5000的员工的信息。*反义运算符select * from T_Employeewhere FAge!=22 AND FSalary!2000 ;注：检索所有年龄不等于22岁并且工资不小于2000员的信息。 不等于= 不小于NOT 运算符用来将一个表达式的值取反select * from T_Employeewhere NOT(FAge=22) AND NOT(FSalary1000 ;*数据处理函数LEN 计算字符串长度的函数 select FName,LEN(FName) AS namelength from T_Employeewhere FName IS NOT NULLSUBSTRING 取得字符串的子串的函数,接受三个参数,第一个为要取的主字符串,第二个为子串的起始位置,第三个为子串的长度 select FName,SUBSTRING(FName,2,3) from T_Employeewhere FName IS NOT NULLSIN 计算正弦函数值的函数ABS 计算绝对值的函数*多个函数可以嵌套使用select FName,FAge,SIN(FAge),ABS(SIN(FAge) from T_Employee*字符串的拼接select 工号为+FNumber+的员工姓名为+FName from T_Employeewhere FName IS NOT NULLselect * from T_Employeewhere FSalary BETWEEN FAge*1.5+2000 AND FAge*1.8+5000 ;注：检索上限为年龄的1.8倍加上5000，下限为年龄的1.5倍加上2000元的员工的信息。select MAX(FSalary/FAge) AS MAXVALUE,MIN(FSalary/FAge) AS MINVALUE from T_Employee ;查询工资年龄指数的最高和最低值。*年龄全部加1UPDATE T_Employee SET FAge=FAge+1 ;*不从实体表中取得数据select 1 ;select LEN(abc)select 1,2,3,a,b,c*联合结果集select FNumber,FName,FAge from T_Employeewhere FAge40UNIONselect FldCardNumber,FName,FAge from T_TempEmployeewhere FAge30 ;*注：联合结果集不必受被联合的多个结果集之间的关系限制，但还是要遵守两个原则：一是每个结果集必须有相同的列数，二是每个结果集的列必须类型相容。（如果需要将未知列补足为一个默认值，那么可以使用常量字段）在默认情况下，UNION运算符合并了两个查询结果集，其中完全重复的数据行被合并为了一条。如果需要在联合结果集中返回所有的记录而不管它们是否唯一，则需要在UNION运算符后使用ALL操作符，例子：select FName,FAge from T_Employee UNION ALL select FName,FAge from T_Employee*联合结果集应用举例*员工年龄报表select 正式员工最高年龄,MAX(FAge) from T_EmployeeUNIONselect 正式员工最低年龄,MIN(FAge) from T_employeeUNIONselect 临时工最高年龄,MAX(FAge) from T_TempEmployeeUNIONselcet 临时工最低年龄,MIN(FAge) from T_TempEmployee*正式员工工资表表要求查询每位正式员工的信息，包括工号、工资，并且在最后一行加上所有员工工资额合计。select FNumber,FSalary from T_EmployeeUNIONselect 工资合计,SUM(FSalary) from T_Employee*打印5以内自然数的平方select 1,1*1UNIONselect 2,2*2UNIONselect 3,3*3UNIONselect 4,4*4UNIONselect 5,5*5*列出员工姓名要求列出公司中所有员工（包括临时工）的姓名，将重复的姓名过滤掉select FName from T_EmployeeUNIONselect FName from T_TempEmployee*分别列出正式员工和临时工的姓名要求分别列出正式员工和临时工的姓名，要保留重复的姓名select 以下是正式的员工的姓名UNION ALLselect FName from T_EmployeeUNION ALLselect 以下是临时工的姓名UNION ALLselect FName from T_TempEmployee函数的应用*数学函数（空，以后补齐）*字符串函数（空，以后补齐）*日期时间函数日期类型：年-月-日 Date来表示日期类型时间类型：小时-分-秒 Time来表示时间类型日期时间类型：年-月-日 小时-分-秒 DateTime来表示日期时间类型时间戳类型：比日期时间类型精度要求还要高的日期时间信息， TimeStamp来表示日期时间戳类型*在MYSQL MSSQL DB2中可以用字符串来表示日期时间类型，数据库系统会自动在内部将它们转换为日期时间类型。在MSSQL中，GETDATE() 返回的是包括了日期、时间的时间戳信息， CONVERT (VARCHAR(50),GETDATE(),101) ,可以得到日期时间值的日期部分 01/24/2008CONVERT(VARCHAR(50),GETDATE(),108) ,可以得到日期时间值的时间部分 21：37：19*日期增减在MSSQL中 提供了DATEADD()函数，进行日期时间的加法运算，格式如：DATEADD(datepart,number,date)其中date为待计算的日期，datepart指定要返回心智的日期组成部分，MSSQL 2005中可识别的日期部分及其缩写：取值 说明year 年份quarter 季度month 月份dayofyear 当年度的第几天day 日week 当年度的第几周weekday 星期几hour 小时minute 分second 秒millisecond 毫秒例子：select FBirthDay,DATEADD(YEAR,3,FBirthDay) AS threeyrs,DATEADD(QUARTER,20,FBirthDay) AS ttqutrs,DATEADD(MONTH,68,FBirthDay) AS sxtmonths,DATEADD(WEEK,-1000,FBirthDay) AS thweeikfrom T_Person ; 注：计算每个人出生后3年、20个季度、68个月以及1000个周前的日期。*计算日期差额在MSSQL中提供了DATEDIFF()函数用于计算两个日期之间的差额，格式：DATEDIFF(datepart，startdate,enddate) 其中参数datepart为计算差额时使用的单位，startdate为起始日期，enddate为结束日期。例子：select FRegDay,FBirthDay,DATEDIFF(WEEK,FBirthDay,FRegDay) from T_Person ;注：计算注册日期和出生日期之间的周数差额。*计算一个日期是星期几在MSSQL中提供了DATENAME函数，这个函数可以返回一个日期的特定部分，并且尽量用名称来表述这个特定部分，格式：DATENAME(datepart，date) ；其中参数date为待计算日期，date参数也可以是日期格式的字符串；参数datepart指定要返回的日期部分的参数。例子：select FBirthDay,DATENAME(Weekday,FBirhtDay),FRegDay,DATENAME(DW,FRegDay)from T_Person注：计算出生日期和注册日期各是星期几；*取得日期的指定部分，比如检索本年的每个月的16日的销售量在MSSQL中使用前面介绍了的DATENAME()函数，使用它可以提取日期的任意部分例子：select FBirthDay, DATENAME(year,FBirthDay) as y, DATENAME(dayofyear,FBirthDay) as d, DATENAME(week,FBirthDay) as u from T_Person; 注：提取每个人员的出生年份、出生是是当年的第几天、出生时是当年的第几周； 在MSSQL中还提供了一个DATEPART()函数，这个函数也可以用来返回一个日期的特定部分，格式：DATEPART(datepart，date) 其中参数date为待计算日期，date参数也可以是日期格式的字符串，参数datepart指定要返回的日期部分参数，显然使用dayofyear做为datepart参数调用DATEPART()函数就可以得到一个日期是当年的第几天；使用year做为datepart参数调用DATEPART()函数就可以得到一个日期的年份；以此类推。例子：select FBirthDay,DATEPART(Dayofyear,FBirthDay),FRegDay,DATEPART(YEAR,FRegDay)from T_Person ; 计算出生日期是当年第几天以及注册日期中的年份部分；DATEPART() 和 DATENAME() 的不同：DATEPART()函数返回值是数字， DATENAME()函数则会将尽可能的以名称的方式做为返回值。*其他函数*类型转换 在MSSQL中提供了CAST()和CONVERT()两个函数进行类型转换，CAST()是符合ANSI SQL99的函数，CONVERT()是符合ODBC标准的函数，格式：CAST(expression AS data_type) CONVERT(data_type,expression) 参数expression为待进行类型转换的表达式，而type为转换的目标类型，例子：select CAST(-30 AS INTEGER) AS i, CONVERT(DECIMAL,3.1415926) AS d, CONVERT(DATETIME,2008-08-08 08:09:10) AS dt 注：以整形、数值、日期时间类型为目标类型的数据转换例子2：select FIdNumber, RIGHT(FIdNumber,3) AS 后三位， CAST(RIGHT(FIdNumber,3) AS INTEGER) AS 后三位的整数形式 CAST(RIGHT(FIdNumber,3) AS INTEGER)+1 AS 后三位数加1， CONVERT(INTEGER,RIGHT(FIdNumber,3)/2 AS 后三位除以2 from T_Person*空值处理COALESCE()函数，这个函数主要用来进行空值处理，COALESCE(expression,value1,value.,valuen) 注：第一个参数expression为待检测的表达式，而其后的参数个数不定。COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式，如果expression不为空值则返回expression；否则判断value1是否是空值，如果value1不为空值则返回value1；否则判断value2是否是空值，如果不为空值则返回value3，以此类推。例子：select FName,FBirthDay,FRegDay,COALESCE(FBirthDay,FRegDay,2008-08-08) AS ImportDayfrom T_Person注：如果出生日期不为空则将出生日期作为“重要日期”，如果出生日期为空则判断注册日期是否为空，如果注册日期不为空则将注册日期作为“重要日期”，如果注册日期也为空则将“2008年8月8日”做为重要日期。MSSQL中，COALESCE()函数的简化版：ISNULL(expression,value),这个函数只接受两个变量。例子：select FBirthDay,FRegDayISNULL(FBirthDay,FRegDay) AS ImportDayfrom T_Person ;如果出生日期不为空则将出生日期做为“重要日期”，NULLIF()函数 主流数据库都支持，格式：NULLIF(expression1,exprssion2)如果两个表达式不等价，则NULLIF返回第一个expression1的值，如果两个表达式等价，则NULLIF返回第一个expression1类型的空值CASE函数 有两种用法：*第一种用法:CASE expressionWHEN value1 THEN returnvalue1WHEN value2 THEN returnvalue2WHEN value3 THEN returnvalue3.ELSE defaultreturnvalueEND注：CASE函数对表达式expression进行测试，如果expression等于value1则返回returnvalue1，如果expression等于value2则返回returnvalue2，以此类推，如果不符合所有的WHEN条件，则返回默认值defaultreturnvalue。例子：use test1select id as 编号,name as 姓名,remtent as 备注,(case name when 涂志勇 then 真正的中文名when hh then 错误的英文名字when ttt then 正确的英文名字else 错误的名字end) as 名字判断from dbo.member*第二种用法CASE WHEN condition1 THEN returnvalue1WHEN condition2 THEN returnvalue2WHEN condition3 THEN returnvalue3.ELSE defaultreturnvalueEND其中condition1，condition2，condition3.为条件表达式，CASE函数对各个表达式从前向后进行测试，如果条件condition1为真则返回returnvalue1，否则如果条件condition2为真则返回returnvalue2，否则如果条件condition3为真则返回returnvalue3，。以此类推，如果不符合所有的WHEN条件，则返回默认值defaultreturnvalue。 这种用法中没有限制只能对一个表达式进行判断，因此使用起来更加灵活。例子：select FName,FWeight,(CASE WHEN FWeight50 THEN fat ELSE OK END ) AS isnormalfrom T_Person ;注：判断一个人的体重是否正常，如果体重小于40则认为太瘦，而如果体重大于50则认为太胖，介于40和50之间则认为是正常的。*各数据库系统都有函数为空*MSSQL中自动增长字段在MSSQL中设定一个字段为自动增长字段，只要在表定义中指定字段为IDENTITY即可，格式为IDENTITY(startvalue，step)，其中的startvalue参数值为起始数字，step参数值为步长，即每次