SQL函数和子句.docx
《SQL函数和子句.docx》由会员分享，可在线阅读，更多相关《SQL函数和子句.docx（12页珍藏版）》请在冰豆网上搜索。
SQL函数和子句
在这篇文章里，我们将就其他一些SQL函数和子句进行阐述，供你用于基本的SELECT数据查询中。
SELECT选项精制结果
正如我们从上一篇文章中所读到的那样，SELECT语句具有种类繁多的各类选项，这些选项可以用来控制数据返回的方式。
这些选项以子句、关键词和函数的形式存在。
子句是一种修改结果的语句。
子句不是必要的语句但它对数据的内容及其显示进行了提炼。
WHERE子句就是这样的子句。
关键词触发数据库的内在功能。
这些关键词在有时甚至是查询所必需的。
例如“INSERTINTOtable_name（column1）VALUES（‘data1’）;”语句中的INTO和VALUE就是如此。
我们将了解DISTINCT关键词，它能触发一些非常有用的可选功能。
下面总结了一些最常用的子句、关键词和函数。
然后我会对每一部分举例说明。
ORDERBY–按照指定列排序返回结果的子句
DISTINCT–只返回结果集合内唯一行的关键词
COUNT--返回匹配查询的数据行总数数值的函数
AVG–该函数返回指定列的平均值
SUM–该函数把指定的列中的数字加起来
MIN–该函数返回列中最小的非NULL值
MAX–该函数返回列中的最大值
GROUPBY–按列汇集查询函数结果的子句
用ORDERBY对查询结果排序
ORDERBY子句让数据库对查询结果排序，这样你就无须自己编写应用程序进行“手工”排序了。
ORDERBY子句必须放在查询语句的结尾。
其基本用法如下：
SELECT*FROMContactsORDERBYfirst_name;
你可以随意在任何选择语句中使用ORDERBY子句返回多列结果。
你还可以用它连接其他子句：
SELECTfirst_name,last_nameFROMContactsWHEREfirst_nameBETWEEN‘a’AND‘k’ORDERBYlast_name;
你可以对多列数据排序。
优先顺序按从左到右依次降低，所以查询语句中各列的排列顺序很重要。
SELECT*FROMContactsORDERBYcompany,last_name,first_name;
查询结果默认按数字或者字母的升序排序。
你可以在ORDERBY子句后面加上DESC关键词改成降序排列。
在下面的例子中，最高的net_amount排在最先（降序）。
假如两行或者两行以上数据都包含了同样的net_amount值，那么同行中last_name值在字母表中最先出现的排先，因为last_name一列还是按照升序排序的。
SELECT*FROMSalesORDERBYnet_amountDESC,last_name,first_name;
在按照定义的列名排序以后，大多数数据库随后将按照数据表内的第一列排序然后顺序向右再排序。
具体的实现各有变化，因此，如果排序在应用中比较重要那么你应该明确地定义所要排序的列。
另外一值得注意的问题是，采用ORDERBY子句（以及WHERE子句），你正在用来排序结果的数据列并不一定得是返回结果集合的一部分。
只要所有引用的列都在数据表内存在则下例完全有效：
SELECTcompany,first_name,net_amountFROMSalesORDERBYstart_date,last_name;
DISTINCT返回不重复结果
DISTINCT关键词只返回结果集合内不重复的数据行。
例如，有时你可能需要找出Sales表内的公司，但是你又不想看见每个条目。
于是你可以用DISTINCT对应每一公司名返回一行数据：
SELECTDISTINCTcompanyFROMSales;
在使用DISTINCT时，它适用于所有的请求列。
如果你打算列出表内的所有销售人员和他们所代表的公司而非每一销售记录，那么你可以使用下列语句。
注意，这样操作还可能返回同一公司的若干条目等等。
SELECTDISTINCTcompany,last_name,first_nameFROMSales;
你还可以在对结果缩小范围和进行排序时结合SELECT语句使用DISTINCT。
为了确定显示的内容，数据库首先会证实精练的请求是否匹配数据行，然后应用DISTINCT功能。
在全部结果集合都得以确定之后即处理ORDERBY子句。
如下例所示，只有net_amount大于100的数据行才被返回。
由于DISTINCT保留遇见的第1个匹配查询条件的数据行而丢弃其他匹配行，所以ORDERBY语句所引用的net_amount看起来就好象产生了随机的结果。
SELECTDISTINCTcompany,last_name,first_nameFROMSalesWHEREnet_amount>100ORDERBYcompany,net_amount;
函数应用逻辑
返回单一值的函数称做聚集函数（aggregatefunction）。
通过应用程序访问下列聚集函数的结果时，包含结果的“字段名”就是你所使用的实际函数。
例如，在分析你的数据库结果时，结果数组的键值可能如下所示：
$keyname=“COUNT（*）”;
$resultkey=“AVG（net_amount）”;
COUNT
COUNT函数计算出结果集合中的数据行数。
和其他函数一样它接受一个参数。
以下的基本示例能告诉你数据表内的行数：
SELECTCOUNT（*）FROMSales;
你也可以用它来计算任何结果集合中的行数。
SELECTCOUNT（*）FROMSalesWHEREnet_amount>100;
如果你想看看某特定列有多少行包含非空值，那你不妨对该列使用COUNT函数。
注意，除非数据库设置为字段为空时缺省填充NULL否则将返回表内数据行的总数。
另外，列出的列在超出一个的情况下会引起错误。
SELECTCOUNT（company）FROMSales;
COUNT还可以用来计算DISTINCT结果集合中的行数。
SELECTCOUNT（DISTINCTcompany,last_name）FROMSales;
COUNT语句通常用在程序中确定FOR循环的循环次数。
AVG
AVG返回某列所有字段的平均值，该列必须是数字数据类型。
该函数用列的名字作为其参数，如果列字段数据类型是非数字类型的则函数返回“0”。
SELECTAVG（net_amount）FROMSales;
你可以结合子句限制该函数的应用范围。
SELECTAVG（net_amount）FROMSalesWHEREcompanyLIKE‘%ABCDCo%’;
就象所有聚集函数一样，ORDERBY语句将被忽略。
SUM
SUM的工作方式和AVG差不多，只不过该函数返回结果集合中所有字段值的和。
SELECTSUM（net_amount）FROMSalesWHEREnet_amount>100;
AVG、SUM、MIN和MAX函数在没有指定列的情况下都会返回错误，所以你不能使用“*”通配符。
MIN
MIN返回指定列中最小的非空值。
如果指定列是数字数据类型则结果将是最小的数字。
如果它是一种字符串数据类型则函数将返回按字母表顺序出现的第1个值。
SELECTMIN（net_amount）FROMSalesWHERElast_name=“Smith”;
SELECTMIN（last_name）FROMSales;
MAX
MAX的工作方式和MIN函数一样，只不过该函数返回最大的非空值。
该函数也可以用于字符串或者数字列
SELECTMAX（net_amount）FROMSales;
SELECTMAX（company）FROMSalesWHEREnet_amount>100;
MAX函数有时还用在包含自动递增键字段的列上确定下一条目的键ID。
除非你正在运行一个非公开的数据库，否则在使用这一信息插入下一条目时务必谨慎，以防其他用户先你执行数据操作。
GROUPBY令函数更有用
虽然以上提到的所有这些函数都能提供相当有用的信息，但是，如果有GROUPBY子句帮忙的话更能让你在列的字段子集中应用这些函数。
不要对你的Sales表中每一家公司一次又一次地执行MAX函数查询——你完全可以带GROUPBY子句获得同样的结果：
SELECTcompany,MAX（net_amount）FROMSalesGROUPBYcompany;
这样做可以获得每家公司net_amount的的最大值。
在选择多列名的时候也可以采用该语句，你还可以用多列来对函数结果分组。
下面的例子演示了以上各种方式。
首先，包括GROUPBY子句可以令你指定要显示的其他列。
然而，你得知道这个例子将返回在组中遇到的第1个last_name值；Sum（net_amount）将显示全部公司的结果而不仅仅针对匹配姓氏的数据行。
这是因为，我们只使用了Company字段来定义我们的组。
SELECTcompany,last_name,SUM（net_amount）FROMSalesGROUPBYcompany;
在上面的例子中，last_name列实际上并没有提供什么有用的信息，但这样做是为了在下一个例子中要用到的功能做准备。
你可以创建多列定义的组。
这样就可以在结果集合中产生针对特定行的函数结果，而结果集合则是由所有指定的GROUPBY列联合起来创建的：
SELECTcompany,AVG（net_amount）,last_nameFROMSalesGROUPBYcompany,last_name;
上面的例子给每家公司中每一姓氏给出了平均的net_amount。
你列出GROUPBY列的顺序控制着结果的排序，但是实际的函数值结果是一样的。
下面的例子表明如何组织结果而不显示分组的列。
在有些场合这样做是很有用的，例如，如果要显示个人的销售量但却不显示姓名就能用上下面的例子了：
SELECTcompany,COUNT（sale_id）FROMSalesGROUPBYcompany,last_name;
限制使用GROUPBY的查询
如你在以上示例中所看到的那样，你可以结合WHERE字句利用以上的概念限制查询的范围。
WHERE子句会首先被计算，然后执行函数。
在使用组的时候就是这样的。
SELECTcompany,AVG（net_amount）,FROMSalesWHEREnet_amount>100GROUPBYcompany;
上面的例子只对那些满足WHERE限制条件的数据行适用AVG函数。
注意，WHERE子句必须放在GROUPBY子句之前。
你还可以用HAVING语句对分组计算之后限制返回的结果集合。
SELECTcompany,AVG（net_amount）,FROMSalesWHERElast_nameBETWEEN‘a’AND‘m’GROUPBYcompanyHAVINGAVG（net_amount）>500;
上面的语句计算每家公司net_amount的平均值，而且只计算那些姓氏满足限制条件的销售人员的销售量，同时只显示大于500的结果。
selecta.编号,a.客户姓名,a.职位代码,b.职位描述,c.企业代码,c.企业类型描述
from表1a,表2b,表3c
wherea.职位代码=b.职位代码anda.企业类型代码=c.企业代码
orderbya.编号
常用SQL语句大全
--语句功能
--数据操作
SELECT--从数据库表中检索数据行和列
INSERT--向数据库表添加新数据行
DELETE--从数据库表中删除数据行
UPDATE--更新数据库表中的数据
--数据定义
CREATETABLE--创建一个数据库表
DROPTABLE--从数据库中删除表
ALTERTABLE--修改数据库表结构
CREATEVIEW--创建一个视图
DROPVIEW--从数据库中删除视图
CREATEINDEX--为数据库表创建一个索引
DROPINDEX--从数据库中删除索引
CREATEPROCEDURE--创建一个存储过程
DROPPROCEDURE--从数据库中删除存储过程
CREATETRIGGER--创建一个触发器
DROPTRIGGER--从数据库中删除触发器
CREATESCHEMA--向数据库添加一个新模式
DROPSCHEMA--从数据库中删除一个模式
CREATEDOMAIN--创建一个数据值域
ALTERDOMAIN--改变域定义
DROPDOMAIN--从数据库中删除一个域
--数据控制
GRANT--授予用户访问权限
DENY--拒绝用户访问
REVOKE--解除用户访问权限
--事务控制
COMMIT--结束当前事务
ROLLBACK--中止当前事务
SETTRANSACTION--定义当前事务数据访问特征
--程序化SQL
DECLARE--为查询设定游标
EXPLAN--为查询描述数据访问计划
OPEN--检索查询结果打开一个游标
FETCH--检索一行查询结果
CLOSE--关闭游标
PREPARE--为动态执行准备SQL语句
EXECUTE--动态地执行SQL语句
DESCRIBE--描述准备好的查询
---局部变量
declare@idchar（10）
--set@id='10010001'
select@id='10010001'
---全局变量
---必须以@@开头
--IFELSE
declare@xint@yint@zint
select@x=1@y=2@z=3
if@x>@y
print'x>y'--打印字符串'x>y'
elseif@y>@z
print'y>z'
elseprint'z>y'
--CASE
usepangu
updateemployee
sete_wage=
case
whenjob_level=’1’thene_wage*1.08
whenjob_level=’2’thene_wage*1.07
whenjob_level=’3’thene_wage*1.06
elsee_wage*1.05--WHILECONTINUEBREAK
declare@xint@yint@cint
select@x=1@y=1
while@x<3
begin
print@x--打印变量x的值
while@y<3
begin
select@c=100*@x+@y
print@c--打印变量c的值
select@y=@y+1
end
select@x=@x+1
select@y=1
end
--WAITFOR
--例等待1小时2分零3秒后才执行SELECT语句
waitfordelay’01:
02:
03’
select*fromemployee
--例等到晚上11点零8分后才执行SELECT语句
waitfortime’23:
08:
00’
select*fromemployee
***SELECT***
select*（列名）fromtable_name（表名）wherecolumn_nameoperatorvalue
ex宿主）
select*fromstock_informationwherestockid=str（nid）
stockname='str_name'
stocknamelike'%findthis%'
stocknamelike'[a-zA-Z]%'---------（[]指定值的范围）
stocknamelike'[^F-M]%'---------（^排除指定范围）
---------只能在使用like关键字的where子句中使用通配符）
orstockpath='stock_path'
orstocknumber<1000
andstockindex=24
notstock***='man'
stocknumberbetween20and100
stocknumberin（10,20,30）
orderbystockiddesc（asc）---------排序，desc-降序，asc-升序
orderby1,2---------by列号
stockname=（selectstocknamefromstock_informationwherestockid=4）
---------子查询
---------除非能确保内层select只返回一个行的值，
---------否则应在外层where子句中用一个in限定符
selectdistinctcolumn_nameformtable_name---------distinct指定检索独有的列值，不重复
selectstocknumber,"stocknumber+10"=stocknumber+10fromtable_name
selectstockname,"stocknumber"=count（*）fromtable_namegroupbystockname
---------groupby将表按行分组,指定列中有相同的值
havingcount（*）=2---------having选定指定的组
select*
fromtable1,table2
wheretable1.id*=table2.id--------左外部连接，table1中有的而table2中没有得以null表示
table1.id=*table2.id--------右外部连接
selectstocknamefromtable1
union[all]-----union合并查询结果集，all-保留重复行
selectstocknamefromtable2
***insert***
insertintotable_name（Stock_name,Stock_number）value（"xxx","xxxx"）
value（selectStockname,StocknumberfromStock_table2）---value为select语句
***update***
updatetable_namesetStockname="xxx"[whereStockid=3]
Stockname=default
Stockname=null
Stocknumber=Stockname+4
***delete***
deletefromtable_namewhereStockid=3
truncatetable_name-----------删除表中所有行，仍保持表的完整性
droptabletable_name---------------完全删除表
***altertable***---修改数据库表结构
altertabledatabase.owner.table_nameaddcolumn_namechar
（2）null.....
sp_helptable_name----显示表已有特征
createtabletable_name（namechar（20）,agesmallint,lnamevarchar（30））
insertintotable_nameselect.........-----实现删除列的方法（创建新表）
altertabletable_namedropconstraintStockname_default----删除Stockname的default约束
***function（/*常用函数*/）***
----统计函数----
AVG--求平均值
COUNT--统计数目
MAX--求最大值
MIN--求最小值
SUM--求和
--AVG
usepangu
selectavg（e_wage）asdept_avgWage
fromemployee
groupbydept_id
--MAX
--求工资最高的员工姓名
usepangu
selecte_name
fromemployee
wheree_wage=
（selectmax（e_wage）
fromemployee）
--STDEV（）
--STDEV（）函数返回表达式中所有数据的标准差
--STDEVP（）
--STDEVP（）函数返回总体标准差
--VAR（）
--VAR（）函数返回表达式中所有值的统计变异数
--VARP（）
--VARP（）函数返回总体变异数
----算术函数----
/***三角函数***/SIN（float_expression）--返回以弧度表示的角的正弦
COS（float_expression）--返回以弧度表示的角的余弦
TAN（float_expression）--返回以弧度表示的角的正切
COT（float_expression）--返回以弧度表示的角的余切
/***反三角函数***/ASIN（float_expression）--返回正弦是FLOAT值的以弧度表示的角
ACOS（float_expression）--返回余弦是FLOAT值的以弧度表示的角
ATAN（float_expression）--返回正切是FLOAT值的以弧度表示的角
ATAN2（float_expression1,float_expression2）
--返回正切是float_expression1/float_expres-sion2的以弧度表示的角
DEGREES（numeric_expression）
--把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/R
升级会员 