经典SQL语句.docx
《经典SQL语句.docx》由会员分享，可在线阅读，更多相关《经典SQL语句.docx（7页珍藏版）》请在冰豆网上搜索。
经典SQL语句
一、基础
1、说明：
创建数据库
CREATEDATABASEdatabase-name
2、说明：
删除数据库
dropdatabasedbname
3、说明：
备份sqlserver
---创建备份数据的device
USEmaster
EXECsp_addumpdevice'disk','testBack','c:
\mssql7backup\MyNwind_1.dat'
---开始备份
BACKUPDATABASEpubsTOtestBack
4、说明：
创建新表
createtabletabname（col1type1[notnull][primarykey],col2type2[notnull],..）
根据已有的表创建新表：
A：
createtabletab_newliketab_old（使用旧表创建新表）
B：
createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly
5、说明：
删除新表
droptabletabname
6、说明：
增加一个列
Altertabletabnameaddcolumncoltype
注：
列增加后将不能删除。
DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：
添加主键：
Altertabletabnameaddprimarykey（col）
说明：
删除主键：
Altertabletabnamedropprimarykey（col）
8、说明：
创建索引：
create[unique]indexidxnameontabname（col….）
删除索引：
dropindexidxname
注：
索引是不可更改的，想更改必须删除重新建。
9、说明：
创建视图：
createviewviewnameasselectstatement
删除视图：
dropviewviewname
10、说明：
几个简单的基本的sql语句
选择：
select*fromtable1where范围
插入：
insertintotable1（field1,field2）values（value1,value2）
删除：
deletefromtable1where范围
更新：
updatetable1setfield1=value1where范围
查找：
select*fromtable1wherefield1like’%value1%’---like的语法很精妙，查资料!
排序：
select*fromtable1orderbyfield1,field2[desc]
总数：
selectcountastotalcountfromtable1
求和：
selectsum（field1）assumvaluefromtable1
平均：
selectavg（field1）asavgvaluefromtable1
最大：
selectmax（field1）asmaxvaluefromtable1
最小：
selectmin（field1）asminvaluefromtable1
11、说明：
几个高级查询运算词
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），不消除重复行。
注：
使用运算词的几个查询结果行必须是一致的。
12、说明：
使用外连接
A、left（outer）join：
左外连接（左连接）：
结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL:
selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
B：
right（outer）join:
右外连接（右连接）：
结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：
full/cross（outer）join：
全外连接：
不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
12、分组:
Groupby:
一张表，一旦分组完成后，查询后只能得到组相关的信息。
组相关的信息：
（统计信息）count,sum,max,min,avg分组的标准）
在SQLServer中分组时：
不能以text,ntext,image类型的字段作为分组依据
在selecte统计函数中的字段，不能和普通的字段放在一起；
13、对数据库进行操作：
分离数据库：
sp_detach_db;附加数据库：
sp_attach_db后接表明，附加需要完整的路径名
14.如何修改数据库的名称:
sp_renamedb'old_name','new_name'
二、提升
1、说明：
复制表（只复制结构,源表名：
a新表名：
b）（Access可用）
法一：
select*intobfromawhere1<>1（仅用于SQlServer）
法二：
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.排序字段
具体实现：
关于数据库分页：
declare@startint,@endint
@sqlnvarchar（600）
set@sql=’selecttop’+str（@end-@start+1）+’+fromTwhereridnotin（selecttop’+str（@str-1）+’RidfromTwhereRid>-1）’
execsp_executesql@sql
注意：
在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。
Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。
因为这样可以避免top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）
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、说明：
随机选择记录
19、说明：
删除重复记录
deletefrompeople
wherepeopleIdin（selectpeopleIdfrompeoplegroupbypeopleId
havingcount（peopleId）>1）
androwidnotin（selectmin（rowid）frompeoplegroupbypeopleIdhavingcount（peopleId）>1）
删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
20、说明：
列出数据库里所有的表名
21、说明：
列出表里的所有的列名
22、说明：
列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select中的case。
23、说明：
初始化表table1
24、说明：
选择从10到15的记录
三、技巧
数据开发-经典
SQLServer基本函数
常识
以上信息未下载，详细查阅：
比如现在有一人员表（表名：
peosons）
若想将姓名、身份证号、住址这三个字段完全相同的记录查询出来
selectp1.*
frompersons p1,persons p2
wherep1.id<>p2.id
and p1.cardid=p2.cardidandp1.pname=p2.pnameandp1.address=p2.address
可以实现上述效果．
几个删除重复记录的SQL语句
1.用rowid方法
2.用groupby方法
3.用distinct方法
1。
用rowid方法
据据oracle带的rowid属性，进行判断，是否存在重复,语句如下：
查数据:
select*fromtable1awhererowid!
=（selectmax（rowid）
fromtable1bwherea.name1=b.name1anda.name2=b.name2......）
删数据：
delete fromtable1awhererowid!
=（selectmax（rowid）
fromtable1bwherea.name1=b.name1anda.name2=b.name2......）
2.groupby方法
查数据:
selectcount（num）,max（name）fromstudent--列出重复的记录数，并列出他的name属性
groupbynum
havingcount（num）>1--按num分组后找出表中num列重复，即出现次数大于一次
删数据：
deletefromstudent
groupbynum
havingcount（num）>1
这样的话就把所有重复的都删除了。
3.用distinct方法-对于小的表比较有用
createtabletable_newas selectdistinct* fromtable1minux
truncatetabletable1;
insertintotable1select*fromtable_new;
查询及删除重复记录的方法大全
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select*frompeople
wherepeopleIdin（selectpeopleIdfrompeoplegroupbypeopleIdhavingcount（peopleId）>1）
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
deletefrompeople
wherepeopleIdin（selectpeopleIdfrompeoplegroupbypeopleId
havingcount（peopleId）>1）
androwidnotin（selectmin（rowid）frompeoplegroupbypeopleIdhavingcount（peopleId）>1）
3、查找表中多余的重复记录（多个字段）
select*fromvitaea
where（a.peopleId,a.seq）in（selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount（*）>1）
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
deletefromvitaea
where（a.peopleId,a.seq）in（selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount（*）>1）
androwidnotin（selectmin（rowid）fromvitaegroupbypeopleId,seqhavingcount（*）>1）
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select*fromvitaea
where（a.peopleId,a.seq）in（selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount（*）>1）
androwidnotin（selectmin（rowid）fromvitaegroupbypeopleId,seqhavingcount（*）>1）
（二）
比方说
在A表中存在一个字段“name”，
而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
SelectName,Count（*）FromAGroupByNameHavingCount（*）>1
如果还查性别也相同大则如下:
SelectName,sex,Count（*）FromAGroupByName,sexHavingCount（*）>1
（三）
方法一
declare@maxinteger,@idinteger
declarecur_rowscursorlocalforselect主字段,count（*）from表名groupby主字段havingcount（*）>；1
opencur_rows
fetchcur_rowsinto@id,@max
while@@fetch_status=0
begin
select@max=@max-1
setrowcount@max
deletefrom表名where主字段=@id
fetchcur_rowsinto@id,@max
end
closecur_rows
setrowcount0
方法二
＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，
比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
1、对于第一种重复，比较容易解决，使用
selectdistinct*fromtableName就可以得到无重复记录的结果集。
如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
selectdistinct*into#TmpfromtableName
droptabletableName
select*intotableNamefrom#Tmp
droptable#Tmp
发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。
2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
selectidentity（int,1,1）asautoID,*into#TmpfromtableName
selectmin（autoID）asautoIDinto#Tmp2from#TmpgroupbyName,autoID
select*from#TmpwhereautoIDin（selectautoIDfrom#tmp2）
最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）
（四）
查询重复
select*fromtablenamewhereidin（
selectidfromtablename
groupbyid
havingcount（id）>1
）
升级会员 