经典MSSQL语句大全和常用SQL语句命令的作用.docx
《经典MSSQL语句大全和常用SQL语句命令的作用.docx》由会员分享，可在线阅读，更多相关《经典MSSQL语句大全和常用SQL语句命令的作用.docx（16页珍藏版）》请在冰豆网上搜索。
经典MSSQL语句大全和常用SQL语句命令的作用
经典MSSQL语句大全和常用SQL语句命令的作用
下列语句部分是Mssql语句，不可以在access中使用。
SQL分类：
DDL类型包括数据库、表的创建，修改，删除，声明—数据定义语言（CREATE，ALTER，DROP，DECLARE）
DML类型包括数据表中记录的查询，删除，修改，插入—数据操纵语言（SELECT，DELETE，UPDATE，INSERT）
DCL类型包括数据库用户赋权，废除用户访问权限，提交当前事务，中止当前事务—数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）
首先,简要介绍基础语句：
1、说明：
创建数据库
CREATEDATABASEdb1（db1代表数据库表，可自命名）
2、说明：
删除数据库
dropdatabasedb1（db1代表数据库表，可自命名）
3、说明：
备份sqlserver
---创建备份数据的device
USEmaster
EXECsp_addumpdevice'disk','testBack','c:
\mssql7backup\MyNwind_1.dat'
---开始备份
BACKUPDATABASEpubsTOtestBack
4、说明：
创建新表
createtabletb1（Idintnotnullprimarykey,namevarchar,..）（tb1为数据表名，ID为字段，int为数据类型整型，notnull为数据是否可为空，PrimaryKey为主键设置，其中notnull,primarykey为可选项，字段，数据类型自定义。
）
根据已有的表创建新表：
A：
createtabletab_newliketab_old（使用旧表创建新表）
B：
createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly
5、说明：
删除新表：
droptabletb1
6、说明：
增加一个列：
Altertabletabnameaddcolumncoltype
注：
列增加后将不能删除。
DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：
添加主键：
Altertabletabnameaddprimarykey（ID）（设置某字段为主键，ID可自由设置，主键数据不可重复）
说明：
删除主键：
Altertabletabnamedropprimarykey（ID）（删除某字段主键）
8、说明：
创建索引：
create[unique]indexidxnameontabname（col….）
删除索引：
dropindexidxname
注：
索引是不可更改的，想更改必须删除重新建。
9、说明：
创建视图：
createviewviewnameasselectstatement
删除视图：
dropviewviewname
10、说明：
几个简单的基本的sql语句
选择：
select*fromtable1whereId=1（Id=1为条件语句，根据自己情况自定义）
插入：
insertintotable1（field1,field2）values（value1,value2）
删除：
deletefromtable1where范围
更新：
updatetable1setfield1=value1where范围
查找：
select*fromtable1wherefield1like’%value1%’---like的语法很精妙，查资料!
排序：
select*fromtable1orderbyfield1,field2[desc]
总数：
selectcount*astotalcountfromtable1
求和：
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、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（）
mysqlselect*From表名OrderByrand（）Limitn
Access左连接语法（最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试,现在记下以备后查）
语法selecttable1.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对象，代表一个错误信息。
SQL语句大全精要
DELETE语句
DELETE语句：
用于创建一个删除查询，可从列在FROM子句之中的一个或多个表中删除记录，且该子句满足WHERE子句中的条件，可以使用DELETE删除多个记录。
语法：
DELETE[table.*]FROMtableWHEREcriteria
语法：
DELETE*FROMtableWHEREcriteria='查询的字'
说明：
table参数用于指定从其中删除记录的表的名称。
criteria参数为一个表达式，用于指定哪些记录应该被删除的表达式。
可以使用Execute方法与一个DROP语句从数据库中放弃整个表。
不过，若用这种方法删除表，将会失去表的结构。
不同的是当使用DELETE，只有数据会被删除;表的结构以及表的所有属性仍然保留，例如字段属性及索引。
UPDATE
有关UPDATE，急!
!
!
!
!
!
!
!
!
!
!
在ORACLE数据库中
表A（ID,FIRSTNAME,LASTNAME）
表B（ID,LASTNAME）
表A中原来ID,FIRSTNAME两个字段的数据是完整的
表B中原来ID,LASTNAME两个字段的数据是完整的
现在要把表B中的LASTNAME字段的相应的数据填入到A表中LASTNAME相应的位置。
两个表中的ID字段是相互关联的。
updateaseta.lastname=（selectb.lastnamefrombwherea.id=b.id）
常用sql语句命令的作用
1.查看数据库的版本
select@@version
2.查看数据库所在机器操作系统参数
execmaster..xp_msver
3.查看数据库启动的参数
sp_configure
4.查看数据库启动时间
selectconvert（varchar（30）,login_time,120）frommaster..sysprocesseswherespid=1
查看数据库服务器名和实例名
print'ServerName...............：
'+convert（varchar（30）,@@SERVERNAME）
print'Instance..................：
'+convert（varchar（30）,@@SERVICENAME）
5.查看所有数据库名称及大小
sp_helpdb
重命名数据库用的SQL
sp_renamedb'old_dbname','new_dbname'
6.查看所有数据库用户登录信息
sp_helplogins
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
更改某个数据对象的用户属主
sp_changeobjectowner[@objectname=]'object',[@newowner=]'owner'
注意：
更改对象名的任一部分都可能破坏脚本和存储过程。
把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
查看某数据库下,对象级用户权限
sp_helprotect
7.查看链接服务器
sp_helplinkedsrvlogin
查看远端数据库用户登录信息
sp_helpremotelogin
8.查看某数据库下某个数据对象的大小
sp_spaceused@objname
还可以用sp_toptables过程看最大的N（默认为50）个表
查看某数据库下某个数据对象的索引信息
sp_helpindex@objname
还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex@objname
clustered索引是把记录按物理顺序排列的，索引占的空间比较少。
对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。
查看某数据库下某个数据对
升级会员 