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

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

《MSSQL数据库各种语句学习资料.docx》由会员分享，可在线阅读，更多相关《MSSQL数据库各种语句学习资料.docx（17页珍藏版）》请在冰豆网上搜索。

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

数据库（catalog）

表（table）

列（column）或叫字段（field）

数据类型（datatype）

记录（record）或叫行（row）

主键（PrimaryKey）

索引（index）

表关联：

这种将两张表通过字段关联起来的方式就被称为“表关联”，关联到其他表主键的字段被称为“外键”

例子:

select*fromemployeeswhereage<18

deletefromemployeeswhereposition=‘名誉总裁’

createtableT_person（FNameVarchar（20）,FAgeint,FRemarkVarchar（20）,primarykey（FName））;

createtableT_Debt（FNumberVarchar（20）,FAmountNumeric（10,2）NOTNULL,FPersonvarchar（20）,PrimaryKey

（FNumber）,foreignkey（FPerson）referencesT_Person（FName））;

insertintoT_person（FName,FAge,FRemark）values（'tom',18,'USA'）注：

在插入数据的时候某些字段没有值，我们可以忽略这些

字段，例子：

insertintoT_Person（FAge,FName）values（22,'lxf'）

说明：

Numeric（10,2）指定字段是数字型，长度为10位，小数为两位

foreignkey（FPerson）外部约束主键为FPerson

说明：

增加一个列

Altertabletabnameaddcolumnnametype

例子：

altertabledbo.T_PersonaddFcityvarchar（20）

*非空约束对数据插入或更新的影响

如果对一个字段添加了非空约束，那么我们是不能向这个字段中插入或更新为NULL值的。

*主键对数据插入或更新的影响

主键是在同一张表中必须是唯一的，如果在进行数据插入或更新的时候指定的主键与表中已有的数据重复的话则会导致违反主键

约束的异常。

*外键对数据插入或更新的影响

外键是指向另一个表中已有的数据的约束，因此外键值必须是在目标表中存在的。

如果插入或更新的数据在目标表中不存在的话

则会导致违反外键约束异常。

**UPDATE

updateT_Person

setFRemark='sonin'

updateT_Person

setFAge=12

whereFName='tom'

updateT_Person

setFAge=22

whereFName='jim'orFName='LXF'

**DELETE

deletefromT_Person;删除T_Person表中的所有数据

droptableT_Person;删除表中的所有数据，及把表结构全部删除。

deletefromT_PersonwhereFAge>20orFRemark='Mars'

********数据检索

select*fromT_Employee

selectFNumber,FName,FAge,FSalaryfromT_Employee

selectFNumberas编号，FNameas姓名，FAgeas年龄fromT_Employee（其中的‘as’不是必须的，是可以省略的）

select*fromT_Employee

whereFSalary<5000orFAge>25;

几种聚合函数：

MAX计算字段最大值

MIN计算字段最小值

AVG计算字段平均值

SUM计算字段合计值

COUNT统计数据条数

selectMAX（FSalary）fromT_Employee

whereFAge>25注：

查询年龄大于25岁的员工的最高工资。

selectMAX（FSalary）asMAX_SALARYfromT_Employee

whereFAge>25

selectAVG（FAge）fromT_Employee

whereFSalary>3800注：

统计工资大于3800元的员工的平均年龄。

selectSUM（FSalary）fromT_Employee;注：

统计应支出工资的总额。

selectMIN（FSalary）,MAX（FSalary）fromT_Employee;注：

多次使用聚合函数，统计公司的最低工资和最高工资。

selectCOUNT（*）,COUNT（FNumber）fromT_Employee;注：

COUNT（*）统计的是结果集的总条数，而COUNT（FNumber）统计的则是除了

结果集中FNumber字段不为空值（也就是不等于NULL）的记录的总条数。

*****排序

select*fromT_Employee

orderbyFAgeASC注：

按升序排列，ASC是可以省略的

select*fromT_Employee

orderbyFAgeDESC注：

按降序排列，

select*fromT_Employee

orderbyFAgeDESC,FSalaryDESC;注：

orderby允许指定多个排序列，首先按第一个排序，分不出的按第二个排序。

****select*fromT_Employee

whereFAge>23

orderbyFAgeDESC,FSalaryDESC;注：

ORDERBY子句要放到where子句后，不能颠倒它们的顺序。

*******通配符过滤SQL中的通配符过滤使用LIKE关键字。

注：

使用通配符时，数据库要对全表进行扫描，所以速度非常慢，不要

过分使用通配符。

1.单字符匹配

select*fromT_Employee

whereFNameLIKE'_erry';注：

以任意字符开头，剩余部分为“erry”。

select*fromT_Employee

whereFNameLIKE'__n_';注：

检索长度为4，第三个字符为“n”，其他字符为任意字符的姓名。

2.多字符匹配

select*fromT_Employee

whereFNameLIKE'T%';注：

检索以“T”开头，长度任意，

select*fromT_Employee

whereFNameLIKE'%n%';注：

检索姓名中包含字母“n”的员工信息

select*fromT_Employee

whereFNameLIKE'%n_';注：

检索最后一个字符为任意字符，倒数第二个字符为“n”长度任意的字符串。

select*fromT_Employee

whereFNameLIKE'[SJ]%';注：

检索的是以“S”或者“J”开头，长度任意的数据

select*fromT_Employee

whereFNameLIKE'[^SJ]%';注：

否定符“^”是来对集合取反，即检索的是不以“S”或者“J”开头，长度任意的数据

******空值检测

select*fromT_Employee

whereFNameISNULL;注：

不能使用普通的等于运算符进行判断，而要使用ISNULL关键字。

select*fromT_Employee

whereFNameISNOTNULL;注：

检索FName字段不为空的数据。

select*fromT_Employee

whereFNameISNOTNULLANDFSalary<5000;注：

查询所有姓名已知且工资小于5000的员工的信息。

*****反义运算符

select*fromT_Employee

whereFAge!

=22ANDFSalary!

<2000;注：

检索所有年龄不等于22岁并且工资不小于2000员的信息。

<>不等于

<=不大于

>=不小于

NOT运算符用来将一个表达式的值取反

select*fromT_Employee

whereNOT（FAge=22）ANDNOT（FSalary<2000）;注：

检索所有年龄不等于22岁并且工资不小于2000元的信息。

“!

”运算符只能运行MSSQL和DB2两种数据库上，统一运算符可以使用在所有数据库中，建议采用NOT运算符，能比较容易的表达要

实现的需求。

*****多值检测

selectFAge,FNumber,FNamefromT_Employee

whereFAgeIN（23,25,28）;注：

为了解决进行多个离散值的匹配问题，SQL提供了IN语句。

检索年龄为23，25，28的数据。

select*fromT_Employee

whereFAgebetween23and60;注：

检索年龄在23到60岁之间的数据，包括23和60。

select*fromT_Employee

where（FSalarybetween2000and3000）

OR（FSalarybetween5000and8000）;注：

检索所有工资介于2000元到3000元之间以及5000元到8000元的员工信息。

*******数据分组

ALTERTABLET_EmployeeADDFSubCompanyVARCHAR（20）;

ALTERTABLET_EmployeeADDFDepartmentVARCHAR（20）;注：

ALTERADD通过更改、添加、除去列和约束，或者通过启用

或禁用约束和触发器来更改表的定义。

**GROUPBY子句进行分组

selectFAgefromT_Employee

whereFSubCompany='Beijing'

groupbyFAge;注：

采用分组以后的查询结果是以分组形式提供的。

selectFSubCompany,FDepartmentfromT_Employee

groupbyFSubCompany,FDepartment;注：

先根据FSubCompany，再在每个小组内根据FDepartment进行二次分组，查询数据

selectFAge,COUNT（*）ASCountOfThisAgefromT_Employee

GROUPBYFAge;注：

检索每个年龄段的员工的人数

selectFSubCompany,FAge,COUNT（*）ASCountOfThisSubCompAgefromT_Employee

groupbyFSubCompany,FAge

orderbyFSubCompany;注：

统计每个公司的年龄段的人数。

****“COUNT（*）”对每个分组统计总数，这样就可以统计出每个公

司每个年龄段的员工的人数了。

selectFAge,COUNT（*）ASCountOfThisAgefromT_Employee

groupbyFAge

HAVINGCOUNT（*）IN（1,3）;注：

HAVING语句和WHERE几乎是一样的，不过使用WHERE的时候GROUPBY子句要位于WHERE子句之后，

而HAVING时，要在之前。

****在HAVING语句中不能包含未分组的列名。

例：

selectFAge,COUNT（*）ASCountOfThisAgefromT_Employee

whereFNameISNOTNULL

GROUPBYFAge;

*****抑制数据重复

distinct关键字是用来进行重复数据抑制的最简单的功能。

selectDISTINCTFDepartmentfromT_Employee;注：

DISTINCT是对整个结果集进行数据重复抑制的，而不是针对每个列。

计算字段

******常量字段

select'CowNew集团',918000000,FName,FAge,FSubCompanyfromT_Employee

******字段间的计算

selectFNumber,FName,FAge*FSalaryAS'工资指数'fromT_Employee;

select125+521,FNumber,FName,FSalary/（FAge-21）ASFHappyIndexfromT_Employee

select*fromT_Employee

whereFSalary/（FAge-21）>1000;

*****数据处理函数

LEN计算字符串长度的函数

selectFName,LEN（FName）ASnamelengthfromT_Employee

whereFNameISNOTNULL

SUBSTRING取得字符串的子串的函数,接受三个参数,第一个为要取的主字符串,第二个为子串的起始位置,第三个为子串的长度

selectFName,SUBSTRING（FName,2,3）fromT_Employee

whereFNameISNOTNULL

SIN计算正弦函数值的函数

ABS计算绝对值的函数

****多个函数可以嵌套使用

selectFName,FAge,SIN（FAge）,ABS（SIN（FAge））fromT_Employee

****字符串的拼接

select'工号为'+FNumber+'的员工姓名为'+FNamefromT_Employee

whereFNameISNOTNULL

select*fromT_Employee

whereFSalaryBETWEENFAge*1.5+2000ANDFAge*1.8+5000;

注：

检索上限为年龄的1.8倍加上5000，下限为年龄的1.5倍加上2000元的员工的信息。

selectMAX（FSalary/FAge）ASMAXVALUE,MIN（FSalary/FAge）ASMINVALUEfromT_Employee;查询工资年龄指数的最高和最低值

。

***年龄全部加1

UPDATET_EmployeeSETFAge=FAge+1;

*****不从实体表中取得数据

select1;

selectLEN（'abc'）

select1,2,3,'a','b','c'

****联合结果集

selectFNumber,FName,FAgefromT_Employee

whereFAge<30

UNION

selectFldCardNumber,FName,FAgefromT_TempEmployee

whereFAge>40

UNION

selectFldCardNumber,FName,FAgefromT_TempEmployee

whereFAge<30;

**注：

联合结果集不必受被联合的多个结果集之间的关系限制，但还是要遵守两个原则：

一是每个结果集必须有相同的列数，二是

每个结果集的列必须类型相容。

（如果需要将未知列补足为一个默认值，那么可以使用常量字段）

在默认情况下，UNION运算符合并了两个查询结果集，其中完全重复的数据行被合并为了一条。

如果需要在联合结果集中返回所有的记录而不管它们是否唯一，则需要在UNION运算符后使用ALL操作符，

例子：

selectFName,FAgefromT_Employee

UNIONALL

selectFName,FAgefromT_Employee

****联合结果集应用举例

*员工年龄报表

select'正式员工最高年龄',MAX（FAge）fromT_Employee

UNION

select'正式员工最低年龄',MIN（FAge）fromT_employee

UNION

select'临时工最高年龄',MAX（FAge）fromT_TempEmployee

UNION

selcet'临时工最低年龄',MIN（FAge）fromT_TempEmployee

*正式员工工资表表

要求查询每位正式员工的信息，包括工号、工资，并且在最后一行加上所有员工工资额合计。

selectFNumber,FSalaryfromT_Employee

UNION

select'工资合计',SUM（FSalary）fromT_Employee

*打印5以内自然数的平方

select1,1*1

UNION

select2,2*2

UNION

select3,3*3

UNION

select4,4*4

UNION

select5,5*5

***列出员工姓名

要求列出公司中所有员工（包括临时工）的姓名，将重复的姓名过滤掉

selectFNamefromT_Employee

UNION

selectFNamefromT_TempEmployee

***分别列出正式员工和临时工的姓名

要求分别列出正式员工和临时工的姓名，要保留重复的姓名

select'以下是正式的员工的姓名'

UNIONALL

selectFNamefromT_Employee

UNIONALL

select'以下是临时工的姓名'

UNIONALL

selectFNamefromT_TempEmployee

函数的应用

****数学函数

（空，以后补齐）

****字符串函数

（空，以后补齐）

****日期时间函数

日期类型：

年-月-日Date来表示日期类型

时间类型：

小时-分-秒Time来表示时间类型

日期时间类型：

年-月-日小时-分-秒DateTime来表示日期时间类型

时间戳类型：

比日期时间类型精度要求还要高的日期时间信息，TimeStamp来表示日期时间戳类型

***在MYSQLMSSQLDB2中可以用字符串来表示日期时间类型，数据库系统会自动在内部将它们转换为日期时间类型。

在MSSQL中，GETDATE（）返回的是包括了日期、时间的时间戳信息，

CONVERT（VARCHAR（50）,GETDATE（）,101）,可以得到日期时间值的日期部分01/24/2008

CONVERT（VARCHAR（50）,GETDATE（）,108）,可以得到日期时间值的时间部分21：

37：

19

******日期增减

在MSSQL中提供了DATEADD（）函数，进行日期时间的加法运算，格式如：

DATEADD（datepart,number,date）

其中date为待计算的日期，datepart指定要返回心智的日期组成部分，

MSSQL2005中可识别的日期部分及其缩写：

取值说明

year年份

quarter季度

month月份

dayofyear当年度的第几天

day日

week当年度的第几周

weekday星期几

hour小时

minute分

second秒

millisecond毫秒

例子：

selectFBirthDay,DATEADD（YEAR,3,FBirthDay）ASthreeyrs,

DATEADD（QUARTER,20,FBirthDay）ASttqutrs,

DATEADD（MONTH,68,FBirthDay）ASsxtmonths,

DATEADD（WEEK,-1000,FBirthDay）ASthweeik

fromT_Person;

注：

计算每个人出生后3年、20个季度、68个月以及1000个周前的日期。

*****计算日期差额

在MSSQL中提供了DATEDIFF（）函数用于计算两个日期之间的差额，

格式：

DATEDIFF（datepart，startdate,enddate）其中参数datepart为计算差额时使用的单位，startdate为起始日期，enddate

为结束日期。

例子：

selectFRegDay,FBirthDay,DATEDIFF（WEEK,FBirthDay,FRegDay）fromT_Person;注：

计算注册日期和出生日期之间的周数

差额。

*****计算一个日期是星期几

在MSSQL中提供了DATENAME函数，这个函数可以返回一个日期的特定部分，并且尽量用名称来表述这个特定部分，

格式：

DATENAME（datepart，date）；其中参数date为待计算日期，date参数也可以是日期格式的字符串；参数datepart指定要返

回的日期部分的参数。

例子：

selectFBirthDay,DATENAME（Weekday,FBirhtDay）,

FRegDay,DATENAME（DW,FRegDay）

fromT_Person

注：

计算出生日期和注册日期各是星期几；

*****取得日期的指定部分，比如检索本年的每个月的16日的销售量

在MSSQL中使用前面介绍了的DATENAME（）函数，使用它可以提取日期的任意部分

例子：

selectFBirthDay,

DATENAME（year,FBirthDay）asy,

DATENAME（dayofyear,FBirthDay）asd,

DATENAME（week,FBirthDay）asu

fromT_Person;注：

提取每个人员的出生年份、出生是是当年的第几天、出生时是当年的第几周；

在MSSQL中还提供了一个DATEPART（）函数，这个函数也可以用来返回一个日期的特定部分，

格式：

DATEPART（datepart，date）其中参数date为待计算日期，date参数也可以是日期格式的字符串，参数datepart指定要返

回的日期部分参数，

显然使用dayofyear做为datepart参数调用DATEPART（）函数就可以得到一个日期是当年的第几天；使用year做为datepart参数调用

DATEPART（）函数就可以得到一个日期的年份；以此类推。

。

。

例子：

selectFBirthDay,DATEPART（Dayofyear,FBirthDay）,

FRegDay,DATEPART（YEAR,FRegDay）

fromT_Person;计算出生日期是当年第几天以及注册日期中的年份部分；

DATEPART（）和DATENAME（）的不同：

DATEPART（）函数返回值是数字，DATENAME（）函数则会将尽可能的以名称的方式做为返回值。

******其他函数

****类型转换

在MSSQL中提供了CAST（）和CONVERT（）两个函数进行类型转换，CAST（）是符合ANSISQL99的函数，CONVERT（）是符合ODBC标准的函