SQL高级查询.docx
- 文档编号:26172057
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:15
- 大小:25.30KB
SQL高级查询.docx
《SQL高级查询.docx》由会员分享,可在线阅读,更多相关《SQL高级查询.docx(15页珍藏版)》请在冰豆网上搜索。
SQL高级查询
关键字:
sql研究
1.UNION,EXCEPT,INTERSECT运算符
A,UNION运算符
UNION运算符通过组合其他两个结果表(例如TABLE1和TABLE2)并消去表中任何重复行而派生出一个结果表。
当ALL随UNION一起使用时(即UNIONALL),不消除重复行。
两种情况下,派生表的每一行不是来自TABLE1就是来自TABLE2。
B,EXCEPT运算符
EXCEPT运算符通过包括所有在TABLE1中但不在TABLE2中的行并消除所有重复行而派生出一个结果表。
当ALL随EXCEPT一起使用时(EXCEPTALL),不消除重复行。
C,INTERSECT运算符
INTERSECT运算符通过只包括TABLE1和TABLE2中都有的行并消除所有重复行而派生出一个结果表。
当ALL随INTERSECT一起使用时(INTERSECTALL),不消除重复行。
注:
使用运算词的几个查询结果行必须是一致的。
----------------------------------------------------------------------------------------------------
2.外连接
A、leftouterjoin:
左外连接(左连接):
结果集几包括连接表的匹配行,也包括左连接表的所有行。
sql:
selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
B:
rightouterjoin:
右外连接(右连接):
结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
C:
fullouterjoin:
全外连接:
不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
----------------------------------------------------------------------------------------------------
其次,大家来看一些不错的sql语句
1、说明:
复制表(只复制结构,源表名:
a新表名:
b)(Access可用)
法一:
select*intobfromawhere1<>1
法二:
selecttop0*intobfroma
2、说明:
拷贝表(拷贝数据,源表名:
a目标表名:
b)(Access可用)
insertintob(a,b,c)selectd,e,ffromb;
3、说明:
跨数据库之间表的拷贝(具体数据使用绝对路径)(Access可用)
insertintob(a,b,c)selectd,e,ffrombin‘具体数据库’where条件
例子:
..frombin'"&Server.MapPath(".")&"\data.mdb"&"'where..
4、说明:
子查询(表名1:
a表名2:
b)
selecta,b,cfromawhereaIN(selectdfromb)
或者:
selecta,b,cfromawhereaIN(1,2,3)
5、说明:
显示文章、提交人和最后回复时间
selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b
6、说明:
外连接查询(表名1:
a表名2:
b)
selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
7、说明:
在线视图查询(表名1:
a)
select*from(SELECTa,b,cFROMa)Twheret.a>1;
8、说明:
between的用法,between限制查询数据范围时包括了边界值,notbetween不包括
select*fromtable1wheretimebetweentime1andtime2
selecta,b,c,fromtable1whereanotbetween数值1and数值2
9、说明:
in的使用方法
select*fromtable1wherea[not]in(‘值1’,’值2’,’值4’,’值6’)
10、说明:
两张关联表,删除主表中已经在副表中没有的信息
deletefromtable1wherenotexists(select*fromtable2wheretable1.field1=table2.field1)
11、说明:
四表联查问题:
select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....
12、说明:
日程安排提前五分钟提醒
sql:
select*from日程安排wheredatediff('minute',f开始时间,getdate())>5
13、说明:
一条sql语句搞定数据库分页
selecttop10b.*from(selecttop20主键字段,排序字段from表名orderby排序字段desc)a,
表名bwhereb.主键字段=a.主键字段orderbya.排序字段
14、说明:
前10条记录
selecttop10*formtable1where范围
15、说明:
选择在每一组b值相同的数据中对应的a最大的记录的所有信息
(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
selecta,b,cfromtablenametawherea=(selectmax(a)fromtablenametbwheretb.b=ta.b)
16、说明:
包括所有在TableA中但不在TableB和TableC中的行并消除所有重复行而派生出一个结果表
(selectafromtableA)except(selectafromtableB)except(selectafromtableC)
17、说明:
随机取出10条数据
selecttop10*fromtablenameorderbynewid()
18、说明:
随机选择记录
selectnewid()
19、说明:
删除重复记录
Deletefromtablenamewhereidnotin(selectmax(id)fromtablenamegroupbycol1,col2,...)
20、说明:
列出数据库里所有的表名
selectnamefromsysobjectswheretype='U'
21、说明:
列出表里的所有的
selectnamefromsyscolumnswhereid=object_id('TableName')
22、说明:
列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select中的case。
selecttype,sum(casevenderwhen'A'thenpcselse0end),
sum(casevenderwhen'C'thenpcselse0end),
sum(casevenderwhen'B'thenpcselse0end)
FROMtablenamegroupbytype
显示结果:
typevenderpcs
电脑A1
电脑A1
光盘B2
光盘A2
手机B3
手机C3
23、说明:
初始化表table1
TRUNCATETABLEtable1
24、说明:
选择从10到15的记录
selecttop5*from(selecttop15*fromtableorderbyidasc)table_别名orderbyiddesc
随机选择数据库记录的方法(使用Randomize函数,通过SQL语句实现)
对存储在数据库中的数据来说,随机数特性能给出上面的效果,但它们可能太慢了些。
你不能要求ASP“找个随机数”然后打印出来。
实际上常见的解决方案是建立如下所示的循环:
Randomize
RNumber=Int(Rnd*499)+1
WhileNotobjRec.EOF
IfobjRec("ID")=RNumberTHEN
...这里是执行脚本...
endif
objRec.MoveNext
Wend
这很容易理解。
首先,你取出1到500范围之内的一个随机数(假设500就是数据库内记录的总数)。
然后,你遍历每一记录来测试ID的值、检查其是否匹配RNumber。
满足条件的话就执行由THEN关键字开始的那一块代码。
假如你的RNumber等于495,那么要循环一遍数据库花的时间可就长了。
虽然500这个数字看起来大了些,但相比更为稳固的企业解决方案这还是个小型数据库了,后者通常在一个数据库内就包含了成千上万条记录。
这时候不就死定了?
采用SQL,你就可以很快地找出准确的记录并且打开一个只包含该记录的recordset,如下所示:
Randomize
RNumber=Int(Rnd*499)+1
sql="SELECT*FROMCustomersWHEREID="&RNumber
setobjRec=ObjConn.Execute(SQL)
Response.WriteRNumber&"="&objRec("ID")&""&objRec("c_email")
不必写出RNumber和ID,你只需要检查匹配情况即可。
只要你对以上代码的工作满意,你自可按需操作“随机”记录。
Recordset没有包含其他内容,因此你很快就能找到你需要的记录这样就大大降低了处理时间。
--------------------------------------------------------------------------------------
再谈随机数
现在你下定决心要榨干Random函数的最后一滴油,那么你可能会一次取出多条随机记录或者想采用一定随机范围内的记录。
把上面的标准Random示例扩展一下就可以用SQL应对上面两种情况了。
为了取出几条随机选择的记录并存放在同一recordset内,你可以存储三个随机数,然后查询数据库获得匹配这些数字的记录:
sql="SELECT*FROMCustomersWHEREID="&RNumber&"ORID="&RNumber2&"ORID="&RNumber3
假如你想选出10条记录(也许是每次页面装载时的10条链接的列表),
你可以用BETWEEN或者数学等式选出第一条记录和适当数量的递增记录。
这一操作可以通过好几种方式来完成,但是SELECT语句只显示一种可能(这里的ID是自动生成的号码):
sql="SELECT*FROMCustomersWHEREIDBETWEEN"&RNumber&"AND"&RNumber&"+9"
注意:
以上代码的执行目的不是检查数据库内是否有9条并发记录。
随机读取若干条记录,
测试过
Access语法:
SELECTtop10*From表名ORDERBYRnd(id)
sqlserver:
selecttopn*from表名orderbynewid()
mysqlelect*From表名OrderByrand()Limitn
Access左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试,现在记下以备后查)
语法electtable1.fd1,table1,fd2,table2.fd2Fromtable1leftjointable2ontable1.fd1,table2.fd1where...
使用SQL语句用...代替过长的字符串显示
语法:
SQL数据库:
selectcasewhenlen(field)>10thenleft(field,10)+'...'
elsefieldendasnews_name,news_idfromtablename
Access数据库:
SELECTiif(len(field)>2,left(field,2)+'...',field)FROMtablename;
Conn.Execute说明
Execute方法该方法用于执行SQL语句。
根据SQL语句执行后是否返回记录集,该方法的使用格式分为以下两种:
1.执行SQL查询语句时,将返回查询得到的记录集。
用法为:
Set对象变量名=连接对象.Execute("SQL查询语言")
Execute方法调用后,会自动创建记录集对象,并将查询结果存储在该记录对象中,
通过Set方法,将记录集赋给指定的对象保存,以后对象变量就代表了该记录集对象。
2.执行SQL的操作性语言时,没有记录集的返回。
此时用法为:
连接对象.Execute"SQL操作性语句"[,RecordAffected][,Option]
·RecordAffected为可选项,此出可放置一个变量,SQL语句执行后,所生效的记录数会自动保存到该变量中。
通过访问该变量,就可知道SQL语句队多少条记录进行了操作。
·Option可选项,该参数的取值通常为adCMDText,它用于告诉ADO,应该将Execute方法之后的第一个字符解释为命令文本。
通过指定该参数,可使执行更高效。
·BeginTrans、RollbackTrans、CommitTrans方法
这三个方法是连接对象提供的用于事务处理的方法。
BeginTrans用于开始一个事物;RollbackTrans用于回滚事务;
CommitTrans用于提交所有的事务处理结果,即确认事务的处理。
事务处理可以将一组操作视为一个整体,只有全部语句都成功执行后,事务处理才算成功;
若其中有一个语句执行失败,则整个处理就算失败,并恢复到处里前的状态。
BeginTrans和CommitTrans用于标记事务的开始和结束,在这两个之间的语句,就是作为事务处理的语句。
判断事务处理是否成功,可通过连接对象的Error集合来实现,若Error集合的成员个数不为0,则说明有错误发生,事务处理失败。
Error集合中的每一个Error对象,代表一个错误信息。
-------------------------------------------------------------------------------------------------
transact---sql高级查询(下)
5:
使用having关键字来筛选结果
6:
使用compute和computeby子句
7:
使用嵌套查询
8:
分布式查询
E:
使用having关键字来筛选结果
当完成对数据结果的查询和统计后,可以使用having关键字来对查询和计算的结果进行一步的筛选
例:
检索出work表中学历是大专或者是中专的人数
select学历,count(学历)fromworkgroupby学历having学历in(\'大专\',\'中专\')
说明:
1:
having关键字都与groupby用在一起.
2:
having不支持对列分配的别名
例如:
select学历,\'大于5的人数\'=count(学历)fromworkgroupby学历having大于5的人数>5[错错]
改为:
select学历,\'大于5的人数\'=count(学历)fromworkgroupby学历havingcount(学历)>5
F:
使用compute和computeby
使用compute子句允许同时观察查询所得到各列的数据的细节以及统计各列数据所产生的汇总列
select*fromwork[查询所得到的各列的数据的细节]
computemax(基本工资),min(基本工资)[统计之后的结果]
这个例子中没有使用by关键字,返回的结果是最后添加了一行基本工资的最大值和最小值,也可增加by关键字.
例:
select*fromworkorderby学历
computemax(基本工资),min(基本工资)by学历
比较:
select学历,max(基本工资),min(基本工资)fromworkgroupby学历
说明:
1:
compute子句必须与orderby子句用在一起
2:
compute子句可以返回多种结果集.一种是体现数据细节的数据集,可以按分类要求进行正确的分类;另一种在分类的基础上进行汇总产生结果.
3:
而groupby子句对每一类数据分类之后只能产生一个结果,不能知道细节
G:
使用嵌套查询
查询中再查询,通常是以一个查询作为条件来供另一个查询使用
例:
有work表和部门表
A:
检索出在部门表中登记的所有部门的职工基本资料
select*fromworkwhere部门编号in[notin](select部门编号fromdbo.部门)
B:
检索出在work表中每一个部门的最高基本工资的职工资料
select*fromworkawhere基本工资=(selectmax(基本工资)fromworkbwherea.部门名称=b.部门名称)
说明:
由外查询提供一个部门名称给内查询,内查询利用这个部门名称找到该部门的最高基本工资,然后外查询根据基本工资判断是否等于最高工资,如果是的,则显示出来.
相当于:
select*fromwork,(select部门名称,max(基本工资)as基本工资fromworkgroupby部门名称ast)wherework.基本工资=t.基本工资andwork.部门名称=t.部门名称
C:
用嵌套work表和嵌套部门表,在嵌套work表中检索出姓名和职工号都在嵌套部门存在的职工资料
select*from嵌套workwhere职工号in(select职工号from嵌套部门)and姓名in(select姓名from嵌套部门)[察看结果,分析原因]
改:
select*from嵌套worka,嵌套部门bwherea.职工号=b.职工号anda.姓名=b.姓名
改:
select*from嵌套workwhere职工号=(select职工号from嵌套部门)and姓名=(select姓名from嵌套部门)[行吗?
为什么,分析原因?
]
在嵌套中使用exists关键字[存在]
例:
1:
用嵌套work表和嵌套部门表,在嵌套work表中检索出姓名和职工号都在嵌套部门存在的职工资料
select*from嵌套workawhereexists(select*from嵌套部门bwherea.姓名=b.姓名anda.职工号=b.职工号)
2:
在work表检索出在部门表没有的职工
select*fromworkwherenotexists(select*from部门where部门.部门编号=work.部门编号)
能否改成:
select*fromworkwhereexists(select*from部门where部门.部门编号<>work.部门编号)
在列清单中使用select
例:
1:
在work1表和部门表中检索出所有部门的部门名称和基本工资总和
select部门名称,(selectsum(基本工资)fromwork1bwherea.部门编号=b.部门编号)from部门a
2:
检索各部门的职工人数
select部门编号,部门名称,(selectcount(职工号)fromwork1awherea.部门编号=b.部门编号)as人数from部门b
3:
在商品表和销售表中查询每一职工的姓名,所属部门,销售总量
select姓名,所属部门,(selectsum(销售量)from商品销售awherea.职工号=b.职工号)as销售总量from嵌套部门b
H:
分布式查询
我们以前的查询都只是基于一个服务器中的一个数据库的查询,如果一个查询是要跨越一个服务器,像这样的查询就是分布式查询,那么我们以看到分布查询就是数据源自于两个服务器.要进行分布式查询必须先创建一个“链接服务器”,以便让本地的用户能够映射到过程服务器.
“链接服务器”的创立
A:
在“链接服务器”里面输入以后为了方便访问该链接服务器的名称[任意]
B:
在“提供程序名称”里面选择“MicrosoftOLEDBProviderforSQLServer”
C:
在“数据源”里面输入服务器的网络名
D:
本
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 高级 查询