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

1、【精品文档】如有侵权，请联系网站删除，仅供学习与交流MSSQL数据库各种语句学习资料.精品文档.数据库（catalog）表（table）列（column） 或叫 字段（field） 数据类型（datatype）记录（record） 或叫 行（row）主键（PrimaryKey）索引（index）表关联：这种将两张表通过字段关联起来的方式就被称为“表关联”，关联到其他表主键的字段被称为“外键”例子:select * from employees where age<18 delete from employees where position=名誉总裁create table T_pers

2、on (FName Varchar(20),FAge int,FRemark Varchar(20),primarykey(FName);create table T_Debt(FNumber Varchar(20),FAmount Numeric(10,2) NOT NULL,FPerson varchar(20),PrimaryKey(FNumber),foreignkey(FPerson) references T_Person(FName);insert into T_person(FName,FAge,FRemark)values('tom',18,'USA&

3、#39;) 注：在插入数据的时候某些字段没有值，我们可以忽略这些字段，例子：insert into T_Person(FAge,FName) values(22,'lxf')说明：Numeric(10,2) 指定字段是数字型，长度为10位，小数为两位 foreignkey(FPerson)外部约束主键为FPerson说明：增加一个列Alter table tabname add columnname type例子：alter table dbo.T_Person add Fcity varchar(20)*非空约束对数据插入或更新的影响 如果对一个字段添加了非空约束，那么我们是

4、不能向这个字段中插入或更新为NULL值的。*主键对数据插入或更新的影响 主键是在同一张表中必须是唯一的，如果在进行数据插入或更新的时候指定的主键与表中已有的数据重复的话则会导致违反主键约束的异常。*外键对数据插入或更新的影响 外键是指向另一个表中已有的数据的约束，因此外键值必须是在目标表中存在的。如果插入或更新的数据在目标表中不存在的话则会导致违反外键约束异常。*UPDATEupdate T_Person set FRemark='sonin'update T_Personset FAge=12where FName='tom'update T_Personse

5、t FAge=22where FName='jim' or FName='LXF'*DELETEdelete from T_Person;删除T_Person表中的所有数据drop table T_Person;删除表中的所有数据，及把表结构全部删除。delete from T_Person where FAge>20 or FRemark='Mars'*数据检索select * from T_Employeeselect FNumber,FName,FAge,FSalary from T_Employeeselect FNumber as

6、 编号，FName as 姓名，FAge as 年龄 from T_Employee (其中的as不是必须的，是可以省略的)select * from T_Employeewhere FSalary<5000 or FAge>25;几种聚合函数：MAX 计算字段最大值MIN 计算字段最小值AVG 计算字段平均值SUM 计算字段合计值COUNT 统计数据条数select MAX(FSalary) from T_Employeewhere FAge>25 注：查询年龄大于25岁的员工的最高工资。select MAX(FSalary) as MAX_SALARY from T_Em

7、ployeewhere FAge>25 select AVG(FAge) from T_Employeewhere FSalary>3800 注：统计工资大于3800元的员工的平均年龄。select SUM(FSalary) from T_Employee; 注：统计应支出工资的总额。select MIN(FSalary),MAX(FSalary) from T_Employee; 注：多次使用聚合函数，统计公司的最低工资和最高工资。select COUNT(*),COUNT(FNumber) from T_Employee; 注：COUNT(*)统计的是结果集的总条数，而COUN

8、T(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

9、 FAge>23 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

10、，第三个字符为“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_Employeewh

11、ere 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字段不

12、为空的数据。select * from T_Employeewhere FName IS NOT NULL AND FSalary<5000; 注：查询所有姓名已知 且工资小于5000的员工的信息。*反义运算符select * from T_Employeewhere FAge!=22 AND FSalary!<2000 ;注：检索所有年龄不等于22岁并且工资不小于2000员的信息。<> 不等于<= 不大于>= 不小于NOT 运算符用来将一个表达式的值取反select * from T_Employeewhere NOT(FAge=22) AND NOT(F

13、Salary<2000) ;注：检索所有年龄不等于22岁并且工资不小于2000元的信息。“!”运算符只能运行MSSQL和DB2两种数据库上，统一运算符可以使用在所有数据库中，建议采用NOT运算符，能比较容易的表达要实现的需求。*多值检测 select FAge,FNumber,FName from T_Employeewhere FAge IN(23,25,28) ; 注：为了解决进行多个离散值的匹配问题，SQL提供了IN语句。检索年龄为23，25，28的数据。select * from T_Employeewhere FAge between 23 and 60 ;注：检索年龄在23到

14、60岁之间的数据，包括23和60。select * from T_Employeewhere (FSalary between 2000 and 3000)OR (FSalary between 5000 and 8000) ; 注：检索所有工资介于2000元到3000元之间以及5000元到8000元的员工信息。*数据分组ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20);ALTER TABLE T_Employee ADD FDepartment VARCHAR (20); 注：ALTER ADD 通过更改、添加、除去列和约束，或者通过启用或

15、禁用约束和触发器来更改表的定义。*GROUP BY 子句进行分组 select FAge from T_Employeewhere FSubCompany='Beijing'group by FAge ; 注：采用分组以后的查询结果是以分组形式提供的。select FSubCompany,FDepartment from T_Employeegroup by FSubCompany,FDepartment ; 注：先根据FSubCompany，再在每个小组内根据FDepartment进行二次分组，查询数据select FAge,COUNT(*) AS CountOfThisAg

16、e from T_EmployeeGROUP BY FAge ; 注：检索每个年龄段的员工的人数select FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge from T_Employeegroup by FSubCompany,FAgeorder by FSubCompany ; 注：统计每个公司的年龄段的人数。*“COUNT(*)”对每个分组统计总数，这样就可以统计出每个公司每个年龄段的员工的人数了。select FAge,COUNT(*) AS CountOfThisAge from T_Employeegroup by FAgeHA

17、VING COUNT(*) IN (1,3) ; 注：HAVING语句和WHERE几乎是一样的，不过使用WHERE的时候GROUP BY子句要位于WHERE子句之后，而HAVING时，要在之前。*在HAVING语句中不能包含未分组的列名。例：select FAge,COUNT(*) AS CountOfThisAge from T_Employeewhere FName IS NOT NULLGROUP BY FAge ;*抑制数据重复distinct 关键字是用来进行重复数据抑制的最简单的功能。select DISTINCT FDepartment from T_Employee ; 注：D

18、ISTINCT是对整个结果集进行数据重复抑制的，而不是针对每个列。计算字段*常量字段select 'CowNew集团',918000000,FName,FAge,FSubCompany from T_Employee*字段间的计算select FNumber,FName,FAge*FSalary AS '工资指数' from T_Employee ;select 125+521,FNumber,FName,FSalary/(FAge-21) AS FHappyIndex from T_Employeeselect * from T_Employeewhere F

19、Salary/(FAge-21)>1000 ;*数据处理函数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 计算绝对值的函数*多个函数可以嵌套使用sele

20、ct 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) A

21、S 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 FAge<30UNIONselect FldCardNumber,FN

22、ame,FAge from T_TempEmployeewhere FAge>40UNIONselect FldCardNumber,FName,FAge from T_TempEmployeewhere FAge<30 ;*注：联合结果集不必受被联合的多个结果集之间的关系限制，但还是要遵守两个原则：一是每个结果集必须有相同的列数，二是每个结果集的列必须类型相容。（如果需要将未知列补足为一个默认值，那么可以使用常量字段）在默认情况下，UNION运算符合并了两个查询结果集，其中完全重复的数据行被合并为了一条。如果需要在联合结果集中返回所有的记录而不管它们是否唯一，则需要在UNION运

23、算符后使用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 '临时工最低年龄&

24、#39;,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*列出员工姓名要求列出公司中所有员工（包括临时工）的姓名，将重

25、复的姓名过滤掉select FName from T_EmployeeUNIONselect FName from T_TempEmployee*分别列出正式员工和临时工的姓名要求分别列出正式员工和临时工的姓名，要保留重复的姓名select '以下是正式的员工的姓名'UNION ALLselect FName from T_EmployeeUNION ALLselect '以下是临时工的姓名'UNION ALLselect FName from T_TempEmployee函数的应用*数学函数（空，以后补齐）*字符串函数（空，以后补齐）*日期时间函数日期类型：年

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

27、,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 FBir

28、thDay,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)

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

30、参数。例子：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

31、from T_Person; 注：提取每个人员的出生年份、出生是是当年的第几天、出生时是当年的第几周； 在MSSQL中还提供了一个DATEPART()函数，这个函数也可以用来返回一个日期的特定部分，格式：DATEPART(datepart，date) 其中参数date为待计算日期，date参数也可以是日期格式的字符串，参数datepart指定要返回的日期部分参数，显然使用dayofyear做为datepart参数调用DATEPART()函数就可以得到一个日期是当年的第几天；使用year做为datepart参数调用DATEPART()函数就可以得到一个日期的年份；以此类推。例子：select F

32、BirthDay,DATEPART(Dayofyear,FBirthDay),FRegDay,DATEPART(YEAR,FRegDay)from T_Person ; 计算出生日期是当年第几天以及注册日期中的年份部分；DATEPART() 和 DATENAME() 的不同：DATEPART()函数返回值是数字， DATENAME()函数则会将尽可能的以名称的方式做为返回值。*其他函数*类型转换在MSSQL中提供了CAST()和CONVERT()两个函数进行类型转换，CAST()是符合ANSI SQL99的函数，CONVERT()是符合ODBC标准的函数，格式：CAST(expression

33、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 后三位，

34、 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在内

35、的所有参数中的第一个非空表达式，如果expression不为空值则返回expression；否则判断value1是否是空值，如果value1不为空值则返回value1；否则判断value2是否是空值，如果不为空值则返回value3，以此类推。例子：select FName,FBirthDay,FRegDay,COALESCE(FBirthDay,FRegDay,'2008-08-08') AS ImportDayfrom T_Person注：如果出生日期不为空则将出生日期作为“重要日期”，如果出生日期为空则判断注册日期是否为空，如果注册日期不为空则将注册日期作为“重要日期”，如

36、果注册日期也为空则将“2008年8月8日”做为重要日期。MSSQL中，COALESCE()函数的简化版：ISNULL(expression,value),这个函数只接受两个变量。例子：select FBirthDay,FRegDayISNULL(FBirthDay,FRegDay) AS ImportDayfrom T_Person ;如果出生日期不为空则将出生日期做为“重要日期”，NULLIF()函数 主流数据库都支持，格式：NULLIF(expression1,exprssion2)如果两个表达式不等价，则NULLIF返回第一个expression1的值，如果两个表达式等价，则NULLIF

37、返回第一个expression1类型的空值CASE函数 有两种用法：*第一种用法:CASE expressionWHEN value1 THEN returnvalue1WHEN value2 THEN returnvalue2WHEN value3 THEN returnvalue3ELSE defaultreturnvalueEND注：CASE函数对表达式expression进行测试，如果expression等于value1则返回returnvalue1，如果expression等于value2则返回returnvalue2，以此类推，如果不符合所有的WHEN条件，则返回默认值defaul

38、treturnvalue。例子：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 condit

39、ion2 THEN returnvalue2WHEN condition3 THEN returnvalue3ELSE defaultreturnvalueEND其中condition1，condition2，condition3.为条件表达式，CASE函数对各个表达式从前向后进行测试，如果条件condition1为真则返回returnvalue1，否则如果条件condition2为真则返回returnvalue2，否则如果条件condition3为真则返回returnvalue3，。以此类推，如果不符合所有的WHEN条件，则返回默认值defaultreturnvalue。 这种用法中没有限制只能对一个表达式进行判断，因此使用起来