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

M S S Q L数据库各种语句学习资料

数据库（catalog）

表（table）

列（column）或叫字段（field）

数据类型（datatype）

记录（record）或叫行（row）

主键（PrimaryKey）

索引（index）

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

例子:select * from employees where age

delete from employees where position=‘名誉总裁’

create table T_person (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') 注：在插入数据的时候某些字段没有值，我们可以忽略这些

字段，例子：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)

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

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

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

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

约束的异常。

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

外键是指向另一个表中已有的数据的约束，因此外键值必须是在目标表中存在的。如果插入或更新的数据在目标表中不存在的话

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

**UPDATE

update T_Person

set FRemark='sonin'

update T_Person

set FAge=12

where FName='tom'

update T_Person

set FAge=22

where FName='jim' or FName='LXF'

**DELETE

delete from T_Person;删除T_Person表中的所有数据

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

delete from T_Person where FAge>20 or FRemark='Mars'

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

select * from T_Employee

select FNumber,FName,FAge,FSalary from T_Employee

select FNumber as 编号，FName as 姓名，FAge as 年龄 from T_Employee (其中的‘as’不是必须的，是可以省略的)

select * from T_Employee

where FSalary25;

几种聚合函数：

MAX 计算字段最大值

MIN 计算字段最小值

AVG 计算字段平均值

SUM 计算字段合计值

COUNT 统计数据条数

select MAX(FSalary) from T_Employee

where FAge>25 注：查询年龄大于25岁的员工的最高工资。

select MAX(FSalary) as MAX_SALARY from T_Employee

where FAge>25

select AVG(FAge) from T_Employee

where 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(*)统计的是结果集的总条数，而COUNT(FNumber)统计的则是除了

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

*****排序

select * from T_Employee

order by FAge ASC 注：按升序排列， ASC是可以省略的

select * from T_Employee

order by FAge DESC 注：按降序排列，

select * from T_Employee

order by FAge DESC, FSalary DESC; 注：order by 允许指定多个排序列，首先按第一个排序，分不出的按第二个排序。

**** select * from T_Employee

where 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，第三个字符为“n”，其他字符为任意字符的姓名。

2.多字符匹配

select * from T_Employee

where FName LIKE 'T%' ; 注：检索以“T”开头，长度任意，

select * from T_Employee

where FName LIKE '%n%' ; 注：检索姓名中包含字母“n”的员工信息

select * from T_Employee

where FName LIKE '%n_' ; 注：检索最后一个字符为任意字符，倒数第二个字符为“n”长度任意的字符串。

select * from T_Employee

where FName LIKE '[SJ]%' ;注：检索的是以“S”或者“J”开头，长度任意的数据

select * from T_Employee

where FName LIKE '[^SJ]%' ;注：否定符“^”是来对集合取反，即检索的是不以

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

******空值检测

select * from T_Employee

where FName IS NULL ; 注：不能使用普通的等于运算符进行判断mssql 运算符，而要使用

IS NULL关键字。

select * from T_Employee

where FName IS NOT NULL ; 注：检索FName字段不为空的数据。

select * from T_Employee

where FName IS NOT NULL AND FSalary

资小于5000的员工的信息。

*****反义运算符

select * from T_Employee

where FAge!=22 AND FSalary!

不小于2000员的信息。

不等于

= 不小于

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

select * from T_Employee

where NOT(FAge=22) AND NOT(FSalary

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

实现的需求。

*****多值检测

select FAge,FNumber,FName from T_Employee

where FAge IN(23,25,28) ; 注：为了解决进行多个离散值的匹配问题，SQL提供了IN语句。检索年龄为23，25，28的数据。

select * from T_Employee

where FAge between 23 and 60 ;注：检索年龄在23到60岁之间的数据，包括23和60。

select * from T_Employee

where (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 通过更改、添加、除去列和约束，或者通过启用

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

**GROUP BY 子句进行分组

select FAge from T_Employee

where FSubCompany='Beijing'

group by FAge ; 注：采用分组以后的查询结果是以分组形式提供的。

select FSubCompany,FDepartment from T_Employee

group by FSubCompany,FDepartment ; 注：先根据FSubCompany，再在每个小组内根据FDepartment进行二次分组，查询数据

select FAge,COUNT(*) AS CountOfThisAge from T_Employee

GROUP BY FAge ; 注：检索每个年龄段的员工的人数

select FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge from

T_Employee

group by FSubCompany,FAge

order by FSubCompany ; 注：统计每个公司的年龄段的人数。****“COUNT(*)”对每个分组统计总数，这样就可以统计出每个公

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

select FAge,COUNT(*) AS CountOfThisAge from T_Employee

group by FAge

HAVING COUNT(*) IN (1,3) ; 注：HAVING语句和WHERE几乎是一样的，不过使用WHERE的时候GROUP BY子句要位于WHERE子句之后，

而HAVING时，要在之前。

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

例：select FAge,COUNT(*) AS CountOfThisAge from T_Employee

where FName IS NOT NULL

GROUP BY FAge ;

*****抑制数据重复

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

select DISTINCT FDepartment from T_Employee ; 注：DISTINCT是对整个结果集进行数据重复抑制的，而不是针对每个列。

计算字段

******常量字段

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_Employee

select * from T_Employee

where FSalary/(FAge-21)>1000 ;

*****数据处理函数

LEN 计算字符串长度的函数

select FName,LEN(FName) AS namelength from T_Employee

where FName IS NOT NULL

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

select FName,SUBSTRING(FName,2,3) from T_Employee

where FName IS NOT NULL

SIN 计算正弦函数值的函数

ABS 计算绝对值的函数

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

select FName,FAge,SIN(FAge),ABS(SIN(FAge)) from T_Employee

****字符串的拼接

select '工号为'+FNumber+'的员工姓名为'+FName from T_Employee

where FName IS NOT NULL

select * from T_Employee

where 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 ;查询工资年龄指数的最高和最低值

。

***年龄全部加1

UPDATE T_Employee SET FAge=FAge+1 ;

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

select 1 ;

select LEN('abc')

select 1,2,3,'a','b','c'

****联合结果集

select FNumber,FName,FAge from T_Employee

where FAge

UNION

select FldCardNumber,FName,FAge from T_TempEmployee

where FAge>40

UNION

select FldCardNumber,FName,FAge from T_TempEmployee

where FAge

**注：联合结果集不必受被联合的多个结果集之间的关系限制，但还是要遵守两个原则：一是每个结果集必须有相同的列数，二是

每个结果集的列必须类型相容。（如果需要将未知列补足为一个默认值，那么可以使用常量字段）

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

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

例子：select FName,FAge from T_Employee

UNION ALL

select FName,FAge from T_Employee

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

*员工年龄报表

select '正式员工最高年龄',MAX(FAge) from T_Employee

UNION

select '正式员工最低年龄',MIN(FAge) from T_employee

UNION

select '临时工最高年龄',MAX(FAge) from T_TempEmployee

UNION

selcet '临时工最低年龄',MIN(FAge) from T_TempEmployee

*正式员工工资表表

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

select FNumber,FSalary from T_Employee

UNION

select '工资合计',SUM(FSalary) from T_Employee

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

select 1,1*1

UNION

select 2,2*2

UNION

select 3,3*3

UNION

select 4,4*4

UNION

select 5,5*5

***列出员工姓名

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

UNION

select FName from T_TempEmployee

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

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

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

UNION ALL

select FName from T_Employee

UNION ALL

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

UNION ALL

select FName from T_TempEmployee

函数的应用

****数学函数

（空，以后补齐）

****字符串函数

（空，以后补齐）

****日期时间函数

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

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

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

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

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

在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指定要返回心智的日期组成部分，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 thweeik

from 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)

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!