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

1、MSSQL数据库各种语句学习资料数据库（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_Employeesel

2、ect 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_Employeewher

3、e 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)统计的则是除了结

4、果集中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 F

5、Age 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 *

6、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_Employeewh

7、ere 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

8、的员工的信息。*反义运算符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

9、取得字符串的子串的函数,接受三个参数,第一个为要取的主字符串,第二个为子串的起始位置,第三个为子串的长度 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 NULLsele

10、ct * 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)se

11、lect 1,2,3,a,b,c*联合结果集select FNumber,FName,FAge from T_Employeewhere FAge40UNIONselect FldCardNumber,FName,FAge from T_TempEmployeewhere FAge30 ;*注：联合结果集不必受被联合的多个结果集之间的关系限制，但还是要遵守两个原则：一是每个结果集必须有相同的列数，二是每个结果集的列必须类型相容。（如果需要将未知列补足为一个默认值，那么可以使用常量字段）在默认情况下，UNION运算符合并了两个查询结果集，其中完全重复的数据行被合并为了一条。如果需要在联合结果集中

12、返回所有的记录而不管它们是否唯一，则需要在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

13、) 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 fro

14、m T_EmployeeUNIONselect FName from T_TempEmployee*分别列出正式员工和临时工的姓名要求分别列出正式员工和临时工的姓名，要保留重复的姓名select 以下是正式的员工的姓名UNION ALLselect FName from T_EmployeeUNION ALLselect 以下是临时工的姓名UNION ALLselect FName from T_TempEmployee函数的应用*数学函数（空，以后补齐）*字符串函数（空，以后补齐）*日期时间函数日期类型：年-月-日 Date来表示日期类型时间类型：小时-分-秒 Time来表示时间类型日期时间

15、类型：年-月-日 小时-分-秒 DateTime来表示日期时间类型时间戳类型：比日期时间类型精度要求还要高的日期时间信息， TimeStamp来表示日期时间戳类型*在MYSQL MSSQL DB2中可以用字符串来表示日期时间类型，数据库系统会自动在内部将它们转换为日期时间类型。在MSSQL中，GETDATE() 返回的是包括了日期、时间的时间戳信息， CONVERT (VARCHAR(50),GETDATE(),101) ,可以得到日期时间值的日期部分 01/24/2008CONVERT(VARCHAR(50),GETDATE(),108) ,可以得到日期时间值的时间部分 21：37：19*日

16、期增减在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 threeyr

17、s,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为起始日期，en

18、ddate为结束日期。例子：select FRegDay,FBirthDay,DATEDIFF(WEEK,FBirthDay,FRegDay) from T_Person ;注：计算注册日期和出生日期之间的周数差额。*计算一个日期是星期几在MSSQL中提供了DATENAME函数，这个函数可以返回一个日期的特定部分，并且尽量用名称来表述这个特定部分，格式：DATENAME(datepart，date) ；其中参数date为待计算日期，date参数也可以是日期格式的字符串；参数datepart指定要返回的日期部分的参数。例子：select FBirthDay,DATENAME(Weekday,FB

19、irhtDay),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; 注：提取每个人员的出生年份、出生是是当年的第几天、出生

20、时是当年的第几周； 在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标准的函