SQL入门到深入.docx

SQL入门到深入.docx

《SQL入门到深入.docx》由会员分享，可在线阅读，更多相关《SQL入门到深入.docx（70页珍藏版）》请在冰豆网上搜索。

SQL入门到深入

SQL语法大全

基础

创建数据库

　　创建之前判断该数据库是否存在

　　ifexists（select*fromsysdatabaseswherename='databaseName'）

　　dropdatabasedatabaseName

　　go

　　CreateDATABASEdatabase-name

删除数据库

　　dropdatabasedbname

备份sqlserver

　　---创建备份数据的device

　　USEmaster

　　EXECsp_addumpdevice'disk','testBack','c:

\mssql7backup\MyNwind_1.dat'

　　---开始备份

　　BACKUPDATABASEpubsTOtestBack

创建新表

　　createtabletabname（col1type1[notnull][primarykey],col2type2[notnull],..）

　　根据已有的表创建新表：

　　A：

go

　　use原数据库名

　　go

　　select*into目的数据库名.dbo.目的表名from原表名（使用旧表创建新表）

　　B：

createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly

创建序列

　　createsequenceSIMON_SEQUENCE

　　minvalue1--最小值

　　maxvalue999999999999999999999999999--最大值

　　startwith1--开始值

　　incrementby1--每次加几

　　cache20;

删除新表

　　droptabletabname

增加一个列

　　Altertabletabnameaddcolnamecoltype

删除一个列

　　Altertabletabnamedropcolumncolname

添加主键

　　Altertabletabnameaddprimarykey（col）

　　说明：

删除主键：

Altertabletabnamedropprimarykey（col）

创建索引

　　create[unique]indexidxnameontabname（col…。

）

　　删除索引：

dropindexidxnameontabname

　　注：

索引是不可更改的，想更改必须删除重新建。

创建视图

　　createviewviewnameasselectstatement

　　删除视图：

dropviewviewname

简单基本的sql语句

（1）数据记录筛选：

　　sql="select*from数据表where字段名=字段值orderby字段名[desc]"

　　sql="select*from数据表where字段名like'%字段值%'orderby字段名[desc]"

　　sql="selecttop10*from数据表where字段名orderby字段名[desc]"

　　sql="select*from数据表where字段名in（'值1','值2','值3'）"

　　sql="select*from数据表where字段名between值1and值2"

（2）更新数据记录：

　　sql="update数据表set字段名=字段值where条件表达式"

　　sql="update数据表set字段1=值1,字段2=值2……字段n=值nwhere条件表达式"

　　（3）删除数据记录：

　　sql="deletefrom数据表where条件表达式"

　　sql="deletefrom数据表"（将数据表所有记录删除）

　　（4）添加数据记录：

　　sql="insertinto数据表（字段1,字段2,字段3…）values（值1,值2,值3…）"

　　sql="insertinto目标数据表select*from源数据表"（把源数据表的记录添加到目标数据表）

　　（5）数据记录统计函数：

　　AVG（字段名）得出一个表格栏平均值

　　COUNT（*;字段名）对数据行数的统计或对某一栏有值的数据行数统计

　　MAX（字段名）取得一个表格栏最大的值

　　MIN（字段名）取得一个表格栏最小的值

　　SUM（字段名）把数据栏的值相加

　　引用以上函数的方法：

　　sql="selectsum（字段名）as别名from数据表where条件表达式"

　　setrs=conn.excute（sql）

　　用rs（"别名"）获取统计的值，其它函数运用同上。

　　查询去除重复值：

selectdistinct*fromtable1

　　（5）数据表的建立和删除：

　　CREATETABLE数据表名称（字段1类型1（长度）,字段2类型2（长度）……）

几个高级查询运算词

　　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），不消除重复行。

　　注：

使用运算词的几个查询结果行必须是一致的。

使用外连接

　　A、leftouterjoin：

　　左外连接（左连接）：

结果集既包括连接表的匹配行，也包括左连接表的所有行。

　　SQL:

selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c

　　B：

rightouterjoin:

　　右外连接（右连接）：

结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

　　C：

fullouterjoin：

　　全外连接：

不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

编辑本段判断对象是否存在

判断数据库是否存在

　　ifexists（select*fromsysdatabaseswherename='数据库名'）

　　dropdatabase[数据库名]

判断表是否存在

　　ifnotexists（select*fromsysobjectswhere[name]='表名'andxtype='U'）

　　begin

　　--这里创建表

　　end

判断存储过程是否存在

　　ifexists（select*fromsysobjectswhereid=object_id（N'[存储过程名]'）andOBJECTPROPERTY（id,N'IsProcedure'）=1）

　　dropprocedure[存储过程名]

判断临时表是否存在

　　ifobject_id（'tempdb..#临时表名'）isnotnull

　　droptable#临时表名

判断视图是否存在

　　--SQLServer2000

　　IFEXISTS（SELECT*FROMsysviewsWHEREobject_id='[dbo].[视图名]'

　　--SQLServer2005

　　IFEXISTS（SELECT*FROMsys.viewsWHEREobject_id='[dbo].[视图名]'

判断函数是否存在

　　ifexists（select*fromdbo.sysobjectswhereid=object_id（N'[dbo].[函数名]'）andxtypein（N'FN',N'IF',N'TF'））

　　dropfunction[dbo].[函数名]

获取创建信息

　　SELECT[name],[id],crdateFROMsysobjectswherextype='U'

　　/*

　　xtype的表示参数类型，通常包括如下这些C=CHECK约束D=默认值或DEFAULT约束F=FOREIGNKEY约束L=日志FN=标量函数IF=内嵌表函数P=存储过程PK=PRIMARYKEY约束（类型是K）RF=复制筛选存储过程S=系统表TF=表函数TR=触发器U=用户表UQ=UNIQUE约束（类型是K）V=视图X=扩展存储过程*/

判断列是否存在

　　ifexists（select*fromsyscolumnswhereid=object_id（'表名'）andname='列名'）

　　altertable表名dropcolumn列名

判断列是否自增列

　　ifcolumnproperty（object_id（'table'）,'col','IsIdentity'）=1

　　print'自增列'

　　else

　　print'不是自增列'

　　SELECT*FROMsys.columnsWHEREobject_id=OBJECT_ID（'表名'）

　　ANDis_identity=1

判断表中是否存在索引

　　ifexists（select*fromsysindexeswhereid=object_id（'表名'）andname='索引名'）

　　print'存在'

　　else

　　print'不存在

查看数据库中对象

　　SELECT*FROMsysobjectsWHEREname='对象名'

　　select*fromtable（所要查询的表名）wherecoloum（条件）

编辑本段提升

复制表

　　（只复制结构，源表名：

a新表名：

b）（Access可用）

　　法一：

select*intobfromawhere1<>1

　　法二：

selecttop0*intobfroma

拷贝表

　　（拷贝数据,源表名：

a目标表名：

b）（Access可用）

　　insertintob（x,y,z）selectd,e,ffroma;

跨数据库之间表的拷贝

　　（具体数据使用绝对路径）（Access可用）

　　insertintob（x,y,z）selectd,e,ffromain‘具体数据库’where条件

　　例子：

。

.frombin'"&Server.MapPath（"."&"\data.mdb"&"'where..

子查询

　　（表名1：

a表名2：

b）

　　selecta,b,cfromawhereaIN（selectdfromb或者：

selecta,b,cfromawhereaIN（1,2,3）

显示文章最后时间

　　selecta.title,a.username,b.adddatefromtablea,（selectmax（adddate）adddatefromtablewheretable.title=a.title）b

外连接查询

　　（表名1：

a表名2：

b）

　　selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c

在线视图查询

　　（表名1：

a

　　select*from（Selecta,b,cFROMa）Twheret.a>1;

between的用法

　　between限制查询数据范围时包括了边界值，notbetween不包括

　　select*fromtable1wheretimebetweentime1andtime2

　　selecta,b,c,fromtable1whereanotbetween数值1and数值2

in的使用方法

　　select*fromtable1wherea[not]in（‘值1’，’值2’,’值4’，’值6’）

删除主表没有的信息

　　两张关联表deletefromtable1wherenotexists（select*fromtable2wheretable1.field1=table2.field1

四表联查问题

　　select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....

日程安排提前五分钟

　　SQL:

select*from日程安排wheredatediff（'minute',f开始时间，getdate（））>5

一条sql搞定数据库页

　　selecttop10b.*from（selecttop20主键字段,排序字段from表名orderby排序字段desc）a,表名bwhereb.主键字段=a.主键字段orderbya.排序字段

前10条记录

　　selecttop10*fromtable1where范围

选择排名

　　选择在每一组b值相同的数据中对应的a最大的记录的所有信息（类似这样的用法可以用于论坛每月排行榜，每月热销产品分析,按科目成绩排名，等等。

）

　　selecta,b,cfromtablenametawherea=（selectmax（a）fromtablenametbwheretb.b=ta.b）

派生结果表

　　包括所有在TableA中但不在TableB和TableC中的行并消除所有重复行而派生出一个结果表

　　（selectafromtableAexcept（selectafromtableB）except（selectafromtableC）

随机取出10条数据

　　selecttop10*fromtablenameorderbynewid（）

随机选择记录

　　selectnewid（）

删除重复记录

　　Deletefromtablenamewhereidnotin（selectmax（id）fromtablenamegroupbycol1,col2,...）

列出数据库里的表名

　　selectnamefromsysobjectswheretype='U'

列出表里的所有的

　　selectnamefromsyscolumnswhereid=object_id（'TableName'）

列示排列

　　列示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

初始化表table1

　　TRUNCATETABLEtable1

选择从10到15的记录

　　selecttop5*from（selecttop5*from（selecttop15*fromtableorderbyidasc）table_别名orderbyiddesc）table_2orderbyid

数据类型转换

　　declare@numidint

　　declare@idvarchar（50）

　　set@numid=2005

　　set@id=convert（varchar,@numid）

　　通过上述语句完成数据类型Int转换成varchar，其他转换类似，可参看convert函数

编辑本段技巧

1=1，1=2的使用

　　在SQL语句组合时用的较多

　　“where1=1”是表示选择全部“where1=2”全部不选，

　　如：

　　if@strWhere!

='

　　begin

　　set@strSQL='selectcount（*）asTotalfrom['+@tblName+']where'+@strWhere

　　end

　　else

　　begin

　　set@strSQL='selectcount（*）asTotalfrom['+@tblName+']'

　　end

　　我们可以直接写成

　　set@strSQL='selectcount（*）asTotalfrom['+@tblName+']where1=1and'+@strWhere

收缩数据库

　　--重建索引

　　DBCCREINDEX

　　DBCCINDEXDEFRAG

　　--收缩数据和日志

　　DBCCSHRINKDB

　　DBCCSHRINKFILE

压缩数据库

　　dbccshrinkdatabase（dbname）

　　转移数据库给新用户以已存在用户权限

　　execsp_change_users_login'update_one','newname','oldname'

　　go

检查备份集

　　RESTOREVERIFYONLYfromdisk='E:

\dvbbs.bak'

修复数据库

　　AlterDATABASE[dvbbs]SETSINGLE_USER

　　GO

　　DBCCCHECKDB（'dvbbs',repair_allow_data_loss）WITHTABLOCK

　　GO

　　AlterDATABASE[dvbbs]SETMULTI_USER

　　GO

日志清除

　　SETNOCOUNTON

　　DECLARE@LogicalFileNamesysname,

　　@MaxMinutesINT,

　　@NewSizeINT

　　USEtablename--要操作的数据库名

　　Select@LogicalFileName='tablename_log',--日志文件名

　　@MaxMinutes=10,--Limitontimeallowedtowraplog.

　　@NewSize=1--你想设定的日志文件的大小（M）

　　--Setup/initialize

　　DECLARE@OriginalSizeint

　　Select@OriginalSize=size

　　FROMsysfiles

　　Wherename=@LogicalFileName

　　Select'OriginalSizeof'+db_name（）+'LOGis'+

　　CONVERT（VARCHAR（30）,@OriginalSize）+'8Kpagesor'+

　　CONVERT（VARCHAR（30）,（@OriginalSize*8/1024））+'MB'

　　FROMsysfiles

　　Wherename=@LogicalFileName

　　CreateTABLEDummyTrans

　　（DummyColumnchar（8000）notnull）

　　DECLARE@CounterINT,

　　@StartTimeDATETIME,

　　@TruncLogVARCHAR（255）

　　Select@StartTime=GETDATE（）,

　　@TruncLog='BACKUPLOG'+db_name（）+'WITHTRUNCATE_ONLY'

　　DBCCSHRINKFILE（@LogicalFileName,@NewSize）

　　EXEC（@TruncLog）

　　--Wrapthelogifnecessary.

　　WHILE@MaxMinutes>DATEDIFF（mi,@StartTime,GETDATE（））--timehasnotexpired

　　AND@OriginalSize=（SelectsizeFROMsysfilesWherename=@LogicalFileName）

　　AND（@OriginalSize*8/1024）>@NewSize

　　BEGIN--Outerloop.

　　Select@Counter=0

　　WHILE（（@Counter<@OriginalSize/16）AND（@Counter<50000））

　　BEGIN--update

　　InsertDummyTransVALUES（'FillLog'）

　　DeleteDummyTrans

　　Select@Counter=@Counter+1

　　END

　　EXEC（@Trunc