mysql数据库语句修复及恢复
1申明：成立数据库 CREA TEDA TA BA SEdatabase-name 2申明：删除数据库 dropdatabasdbname 3申明：备份sqlserver ---建立 备份数据的device USEmaster 'testBack',EXECsp_addumpdevic'disk'.'c:mssql7backupMyNwind_1.dat' ---起头 备份 BA CKUPDA TA BA SEpubTOtestBack 4申明：成立新表 col2type2[notnull],creattabltabnamcol1type1[notnull][primarikey]... 遵照已有的表创建新表： A creattabltab_newliketab_old操纵旧表创建新表) col2fromtab_olddefinitonlBcreattabltab_newasselectcol1.y 5申明：删除新表 droptabltabname 6申明：增加一个列 A ltertabltabnamaddcolumncoltype 唯一能改变的增添varchar范例的长度。注：列增加后将不能删除。DB2中列加上后数据典范榜样也不能改变。 7申明：增加主键：Altertabltabnamaddprimarikeicol 申明：删除主键：Altertabltabnamdropprimarikeicol 8申明：成立索引：creat[unique]indexidxnamontabnamcol. 删除索引：dropindexidxname 注：索引是不可变动的想变更必须删除重新建。 9申明：成立视图：creatviewviewnamasselectstatement 删除视图：dropviewviewname 10申明：几个简单的根基的sql语句 抉择：select*fromtable1where规模 field2valuvalue1,拔出：insertintotable1field1.value2 删除：deletfromtable1where规模 更新：updattable1setfield1=value1where规模 查资料查找：select*fromtable1wherefield1like%value1%---like语法很精妙。! field2[desc排序：select*fromtable1orderbyfield1.] 总数：selectcountastotalcountfromtable1 乞降：selectsumfield1assumvalufromtable1 均匀：selectavgfield1asavgvalufromtable1 最大：selectmaxfield1asmaxvalufromtable1 最小：selectminfield1asminvalufromtable1 11申明：几个高档盘问运算词 A UNION运算符 派生表的每一行不是来自 TA BLE1就是来自 TA BLE2UNION运算符通过组合其他两个结果表（比方 TA BLE1和 TA BLE2并消去表中任何频频行而派生出一个结果表。当 ALL随 UNION一起操纵时（即 UNIONALL不消除重复行。两种情况下。 BEXCEPT运算符 EXCEPT运算符通过包含一切在TA BLE1中但不在TA BLE2中的行并消除所有频频行而派生出一个结果表。当 ALL随 EXCEPT一起操纵时 EXCEPTALL不消除重复行。 CINTERSECT运算符 INTERSECT运算符通过只包括 TA BLE1和 TA BLE2中都有的行并消除所有频频行而派生出一个结果表。当 ALL随 INTERSECT一起操纵时 INTERSECTALL不消除重复行。 注：操纵运算词的几个盘问功效行必须是分歧的 12申明：操纵外连接 A leftouterjoin 也包含左连接表的所有行。左外连接（左连接）功效集几包含连接表的匹配行。 a.b,SQL:selecta.a.a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c Brightouterjoin: 也包含右连接表的所有行。右外连接(右连接)功效集既包含连接表的匹配连接行。 Cfull/crossouterjoin 还包含两个连接表中的所有记实。全外连接：不仅包含标识表记标帜连接表的匹配行。 12分组:Groupby: 一旦分组完成后， sum, ntext, 不能和普通的字段放在一路； 13对数据库履行操纵： 附加必要完全的道路名 14.如何改削数据库的称号: 'new_namesp_renamedb'old_name'.' 二、提拔 源表名：a新表名：bAccess可用)1申明：复制表(只复制结构. 法一：select*intobfromawhere1<>1仅用于SQlServer 法二：selecttop0*intobfroma 源表名：a目标表名：bAccess可用)2申明：拷贝表(拷贝数据. b,insertintoba.cselectd,e,ffromb; 3申明：跨数据库之间表的拷贝(具体数据操纵绝对门路)Access可用) b,insertintoba.cselectd,e,ffrombin具体数据库’where前提 例子：..frombin'"&Server.MapPath"."&"data.mdb"&"'where.. 4申明：子查询(表名1a表名2b b,selecta.cfromawhereaINselectdfromb或:b,cfromawhereaIN1,2,3 5申明：展现文章、提交人和最后回覆时候 a.username,selecta.title.b.adddfromtabla,selectmaxadddatadddatfromtablwheretable.title=a.titlb 6申明：外连接查询(表名1a表名2b a.b,selecta.a.a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c 7申明：线视图查询(表名1a b,select*fromSELECTa.cFROMaTwheret.a>1; between限制盘问数据范围时包括了边界值,8申明：between用法.notbetween不包括 select*fromtable1wheretimebetweentime1andtime2 b,selecta.c,fromtable1whereanotbetween数值1and数值2 9申明：in操纵方式 值2,select*fromtable1wherea[not]in值1.值4,值6 删除主表中已经在副表中没有的消息10申明：两张关联表。 deletfromtable1wherenotexistselect*fromtable2wheretable1.field1=table2.field1 11申明：四表联查问题： select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere..... 12申明：日程安排提前五分钟提醒 f开端时候,SQL:select*from日程放置 wheredatediff'minute'.getdat>5 13申明：一条sql语句搞天命据库分页 排序字段 from表名 orderby排序字段 desca,selecttop10b.*fromselecttop20主键字段.表名 bwhereb.主键字段 =a.主键字段 orderbya.排序字段 具体完成： 关于数据库分页： @endin 以是在实际操纵中只有这样的履行非凡的处置惩罚。Rid为一个标识列，注重：top后不能直接跟一个变量。若是top后还有具体的字段，这样做是非常有好处的因为这样可以或许防止 top字段如果是逻辑索引的查问的功效后实际表中的不一致（逻辑索引中的数据有可以或许和数据表中的不一致，而盘问时如果处在索引则首先盘问索引） 14申明：前10条记录 selecttop10*formtable1where规模 每月热销产品阐发,15申明：抉择在每一组b值相同的数据中对应的a最大的记实的所有消息(类似如许的用法可以或许用于论坛每月排行榜.按科目造诣排名,等等. b,selecta.cfromtablenamtawherea=selectmaxafromtablenamtbwheretb.b=ta.b 16申明：包含一切在TableA 中但不在TableB和TableC中的行并消除所有频频行而派生出一个结果表 selectafromtableA exceptselectafromtableBexceptselectafromtableC 17申明：随机存入10条数据 selecttop10*fromtablenamorderbynewid 18申明：随机决定记实 selectnewid 19申明：删除频频记实 deletfromtablenamwhereidnotinselectmaxidfromtablenamgroupbycol1,1.col2,... selectdistinct*intotempfromtablenam2.e 这种做法不适合大容量但数据操作评估：这种把持扳连大批的数据的挪动。 由于某些原由第一次只导入了一部分，但很难鉴定具体地位，这样只要在下一次全部导入，这样也就产生好多反复的字段，怎样删除频频字段3,比方：一个外部表中导入数据。 altertabltablename --增加一个自增列 1add column2,selectmaxcolumn_b altertabltablenamdropcolumncolumn_b 20申明：列出数据库里所有的表名 selectnamefromsysobjectwheretype='U'//U代表用户 21申明：列出内外的一切的列名 selectnamefromsyscolumnwhereid=object_id'TableName' 以type字段排列，22申明：列示typevenderpc字段。case可以或许方便地实现多重抉择，近似select中的case sumcasevenderwhen'A 'thenpcels0end,selecttype.sumcasevenderwhen'C'thenpcels0end,sumcasevenderwhen'B'thenpcels0endFROMtablenamgroupbytype 展现成果： typevenderpcs 电脑 A1 电脑 A1 光盘 B2 光盘 A2 手机 B3 手机 C3 23申明：初始化表table1 PUNCA TETA BLEtable1 24申明：决定从1015记实 selecttop5*fromselecttop15*fromtablorderbyidasctable_别号 orderbyiddesc 三、技术 SQL语句组合时用的较多11=11=2利用。 where1=1表示决定全数 如： 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=1安靖 '+@strWhere2延长数据库 --重建索引 DBCCREINDEX DBCCINDEXDEFRA G --延长数据和日志 DBCCSHRINKDB DBCCSHRINKFILE 3缩短数据库 dbccshrinkdatabasdbname 4转移数据库给新用户以已存在用户权限 'newname',execsp_change_users_login'update_one'.'oldname' go 5搜查备份集 RESTOREVERIFYONLYfromdisk='E:dvbbs.bak' 6修复数据库 A LTERDA TA BA SE[dvbbs]SETSINGLE_USER GO repair_allOw青岛数据恢复 href="http://www.hdd120.net">青岛数据恢复_data_lossWITHTA BLOCDBCCCHECKDB'dvbbs'.K GO A LTERDA TA BA SE[dvbbs]SETMULTI_USER GO 7日志断根 SETNOCOUNTON
@MaxMinutINT.>
USEtablenam--要操作的数据库名 --日志文件名SELECT --Limitontimeallowtowraplog@MaxMinut=10.. Setup/initialize DECLA RE@OriginalSint SELECT@OriginalS=size SELECT'OriginSizeof'+db_name+'LOGis'+ @OriginalS+'8Kpageor' @OriginalSize*8/1024+'MB CREA TETA BLEDummyTrans
@StartTimDA TETIME.>
@NewSizDBCCSHRINKFILE@LogicalFileName. EXEC@TruncLog --Wrapthelogifnecessary. @StartTime,WHILE@MaxMinut>DA TEDIFFmi.GETDA TE--timehanotexpired SELECT@Counter=0 SELECT'FinalSizeof'+db_name+'LOGis'+ size+'8Kpageor' size*8/1024+'MB DROPTA BLEDummyTrans SETNOCOUNTOFF 8申明：变更某个表 'dboexecsp_changeobjectown'tablename'.' 9存储变更全部表 CREA TEPROCEDUREdbo.User_ChangeObjectOwnerBatch
@NewOwnerasNVA RCHA R128 A S DECLA RE@Name DECLA RE@Owner DECLA RE@OwnerName DECLA REcurObjectCURSORFOR
fromsysobjects wheruser_namuid=@OldOwner ordbyname OPEN @OwneFETCHNEXTFROMcurObjectINTO@Name.r WHILE@@FETCH_STA TUS=0 BEGIN if@Owner=@OldOwner begin @NewOwne end @NewOwner,--select@name.@OldOwner @OwneFETCHNEXTFROMcurObjectINTO@Name.r END closcurObject dealloccurObject GO 10SQLSERVER中直接循环写入数据 declar@iint set@i=1 whil@i<30 begin end 案例： 要求就裱中所有沒有及格的成績，有如下表。每次增長0.1基礎上，使他們剛好及格: whilselectminscorefromtb_tabl<60 begin updtb_tablsetscore=score*1.01 wherscore<60 if end 数据开辟-典范 1.按姓氏笔画排序: Select*FromTableNamOrderByCustomerNamCollatChinese_PRC_Stroke_ci_a//从少到多 2.数据库加密: selectencrypt'原始明码' selectpwdencrypt'原始明码' '加密后密码'=1--不异；否则不相同 encrypt'原始明码'selectpwdcompar'原始明码'. selectpwdencrypt'原始明码' '加密后密码'=1--不异；否则不相同selectpwdcompar'原始明码'. 3.取回表中字段:
@sqlnvarchar1000 '+b.namfromsysobjecta,select@list=@list+'.syscolumnbwherea.id=b.idanda.name='表A' len@list-1+'from表Aset@sql='select'+right@list.' exec@sql 4.查抄硬盘分区: EXECmaster..xp_fixeddrives B表是否相等5.比力A.: ifselectchecksum_aggbinary_checksum*fromA print'相称' else print'不相等' 6.杀掉所有的事件探察器进程: DECLA REhcforeachCURSORGLOBA LFORSELECT'kill'+RPIMspidFROMmaster.dbo.sysprocesses N'SQL事件探查器'WHEREprogram_namIN'SQLprofiler'. EXECsp_msforeach_work'?' 7.记录搜刮: 开首到N条记录 SelectTopN*From表 ------------------------------- NM条记录(要有主索引ID SelectTopM-N*From表 WhereIDinSelectTopMIDFrom表)OrderbyID ---------------------------------- N开首记实 SelectTopN*From表 OrderbyIDDesc 案例 表的第一个字段 RecID自增长字段，比方1一张表有一万多条记录。写一个SQL语句，找出表的第31第40个记录。 若是recid表中存在逻辑索引。阐发：如果这样写会产生某些成绩。 而后面的selecttop30recidfromA则在数据表中查找， 处置惩罚计划 就会出现问题1 2 并不知道这个表共有若干好多数据,例2盘问表中的最后以条记录。以及表结构。 set@s='selecttop1*fromT print@s 9获取面前数据库中的所有用户表 selectNamefromsysobjectwherextype='u'andstatus>=0 10获取某一个表的所有字段 selectnamefromsyscolumnwhereid=object_id'表名' selectnamefromsyscolumnwhereidinselectidfromsysobjectwheretype='u'andname='表名' 两种方式的成果不异 11查抄与某一个表相关的视图、存储历程、函数 syscombwherea.id=b.idandb.textlike'%表名%selecta.*fromsysobjecta.' 12查抄面前数据库中所有存储历程 selectnameas存储过程称号 fromsysobjectwherextype='P' 13盘问用户建立的所有数据库 select*frommaster..sysdatabasDwheresidnotinselectsidfrommaster..sysloginwherename='sa' 或 nameASDB_NA MEfrommaster..sysdatabaswheresid<>0x0selectdbid.1 14盘问某一个表的字段和数据类型 data_typfrominformation_schema.columnselectcolumn_name.s whertable_nam='表名' 15不合服务器数据库之间的数据操纵 --成立链接服务器 '',execsp_addlinkedserver 'fals',execsp_addlinkedsrvlogin --盘问示例 select*fromITSV.数据库名.dbo.表名 --导入示例 select*into表 fromITSV.数据库名.dbo.表名 --从此不再操纵时删除链接服务器 'droploginexecsp_dropserver --连接长途/局域网数据(openrowset/openquery/opendatasourc --1openrowset --盘问示例 'sql服务器名 ';'用户名 ';'明码 ',select*fromopenrowset'SQLOLEDB'.数据库名.dbo.表名) --生成本地表 'sql服务器名 ';'用户名 ';'明码 ',select*into表 fromopenrowset'SQLOLEDB'.数据库名.dbo.表名) --把本地表导入短途表 'sql服务器名 ';'用户名 ';'明码 ',insertopenrowset'SQLOLEDB'.数据库名.dbo.表名) select*from本地表 --更新本地表 updatb setb.列A=a.列A 'sql服务器名 ';'用户名 ';'明码 ', ona.column1=b.column1 --openqueri用法必要成立一个连接 --首先成立一个连接成立链接服务器 '',execsp_addlinkedserver --查问 select* --把本地表导入短途表 select*from本地表 --更新本地表 updatb setb.列B=a.列B innjoin本地表 bona.列A=b.列A --3opendatasource/openrowset SELECT --把本地表导入短途表 select*from本地表 SQLServer基础函数 SQLServer基础函数 1.字符串函数 长度与分析用 datalengthChar_expr返回字符串包含字符数,1.但不包含后面的空格 字符串的下标是从“1start为起始位置，length为字符串长度，实际操纵中以lenexpress失掉其长度2,substrexpression,start,length取子串。 还用left于之相反3,rightchar_expr,int_expr返回字符串右边第int_expr个字符。 則返回replacement_valu值，不為空，就返回check_express字符操作类4,isnulcheck_express,replacement_valu若是check_express為空。 Sp_addtyp自定義數據類型5. datetime,比方：EXECsp_addtypbirthday.'NULL' setnocount{on|off6.} 则该设置由于大批量削减了搜集流量，使返回的功效中不包含有关受 Transact-SQL语句影响的行数的消息。如果存储过程中包含的一些语句并不返回许多现实的数据。因此可较着前进机能。SETNOCOUNT设置是执行或运行时设置，而不是分析时设置。 不返回计数（表示受 Transact-SQL语句影响的行数）SETNOCOUNT为 ON时。 返回计数SETNOCOUNT为 OFF时。 知识 SQL盘问中：from后最多可以或许跟若干好多张表或视图：256 先排序，后取SQL语句中出现 Orderby,盘问时。 一个字段的最大容量是8000而对于nvarchar4000,SQL中。因为nvarcharUnicod码。 SQLServer2000同步复制技术实现步调 一、筹办任务 订阅服务器都成立一个同名的window用户,1.颁布发表服务器.并设置相同的明码,做为颁布发表快照文件夹的有效访谒用户 --管理对象 --计算机管理 --用户和组 --右键用户 --新建用户 --建立一个隶属于administr组的上岸window用户（SynUser 新建一个共享目录,2.颁布发表服务器上.做为发布的快照文件的存放目次,操纵: 名为:PU电脑--D:新建一个目录.B --右键这个新建的目次 --属性--共享 --抉择"共享该文件夹" 保证第一步中创建的用户(SynUser具有对该文件夹的所有权限--经由过程"权限"按纽来设置具体的用户权限. --断定 3.设置SQL署理(SQLSERVERA GENT办事的策动用户(宣布/订阅服务器均做此设置) 起头--法式--管理对象--办事 --右键SQLSERVERA GENT --属性--上岸--抉择"此账户" --输入或者决定第一步中创建的window登录用户名（SynUser --"明码"中输入该用户的明码 处置惩罚连接时的权限成绩(宣布/订阅服务器均做此设置)4.设置SQLServer身份验证形式. 企业管理器 --右键SQL实例--属性 --保险性--身份考证 --抉择"SQLServer和 Windows" --断定 5.颁布发表服务器和订阅服务器上互相注册 企业管理器 --右键SQLServer组 --新建SQLServer注册... 输出你要注册的短途服务器名 --增添--下一步--可用的服务器中. 决定第二个"SQLServer身份考证--下一步--连接利用." --下一步--输入用户名和密码（SynUser 也可以或许成立一个新组--下一步--抉择SQLServer组. --下一步--实现 不能用计算机名的,6.对于只能用IP.为其注册服务器别名（此步在实施中没用到 好比, 起头--法式--MicrosoftSQLServer--用户端搜集实用对象 --别号--增添 --搜集库选择"tcp/ip"--服务器别名输入SQL服务器名 --连接参数--服务器名称中输入SQL服务器ip地点 消除抉择"情况决定端口",--若是你点窜了SQL端口.并输入对应的端口号 二、正式设置装备摆设 1配置颁布发表服务器 颁布发表服务器（BCD上执行以下步调掀开企业管理器。: 1从[对象]下拉菜单的[复制]子菜单落第择[配置宣布、订阅服务器和分发]出现配置颁布发表和分发向导 2[下一步]决定披发服务器 可以或许决定把颁布发表服务器自己作为披发服务器或者其他sql服务器（决定本身） 3[下一步]设置快照文件夹 采用默许\servernamePub 4[下一步]自定义配置 让我设置披发数据库属性启用颁布发表服务器或设置颁布发表设置可以或许抉择:. 操纵下列默认设置（保举）否. 5[下一步]设置披发数据库名称和位置 采用默认值 6[下一步]启用颁布发表服务器 决定作为宣布的服务器 7[下一步]决定必要宣布的数据库和发布类型 8[下一步]决定注册订阅服务器 9[下一步]完成设置装备摆设 2成立出版物 颁布发表服务器BCD上 1从[对象]菜单的[复制]子菜单落第择[成立和管理发布]饬令 尔后单击[成立宣布2决定要成立出版物的数据库。] 3[成立颁布发表领导]提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容信息是复制的三个类型。现在选第一个也就是默许的快照发布(其他两个大家可以或许去看看帮助)
SQLSERVER许可在分歧的数据库如 orA CLE或ACCESS之间履行数据复制。 但是这里我决定运转"SQLSERVER2000"数据库服务器 5单击[下一步]系统就弹出一个定义文章的对话框也就是决定要出版的表 注重:如果前面抉择了事务宣布 则再这一步中只能决定带有主键的表 6决定颁布发表名称和描述 7自定义颁布发表属性 带领供应的抉择: 启用匿名订阅和或其他自定义属性将自定义数据挑选. 否 遵照指定方式成立宣布 建议采用自定义的体式格局） 8[下一步]决定遴选宣布的体式格局 9[下一步]可以或许抉择是否允许匿名订阅 则需要在颁布发表服务器上增加订阅服务器1如果决定署名订阅. 方式:[对象]->[复制]->[配置宣布、订阅服务器和分发的属性]->[订阅服务器]中添加 不然在订阅服务器上请求订阅时会出现的提醒:改颁布发表不允许匿名订阅 如果仿照照旧必要匿名订阅则用以下解决办法 [企业管理器]->[复制]->[颁布发表内容信息]->[属性]->[订阅选项]决定允许匿名请求订阅 则配置订阅服务器时不会出现以上提醒2如果决定匿名订阅. 10[下一步]设置快照 代理轨范调剂 11[下一步]完成设置装备摆设 当完成出版物的成立后成立出版物的数据库也就变成了一个共享数据库 罕见据 name,srv1.库名..author有字段:id.phone, name,srv2.库名..author有字段:id.telphone,adress 请求： srv1.库名..author增加记录则srv1.库名..author记录增添 则srv1.库名..author对应字段telphon更新srv1.库名..authorphone字段更新。 --*/ --大体的处理步调 以便在srv1中操作 srv2,--1.srv1上成立连接服务器.实现同步 '',execsp_addlinkedserv'srv2'.'SQLOLEDB','srv2sql实例名或ip' 'false',execsp_addlinkedsrvlogin'srv2'.null,'用户名','明码' go 发动 msdtc漫衍式事务处理办事),--2.srv1和 srv2这两台电脑中.并且设置为自动启动 电脑--控制面板--管理对象--办事--右键 DistributTransactCoordinator--属性--发动--并将策动典范榜样设置为自动启动 go --尔后成立一个作业定时调用下面的同步处理存储过程就行了 企业管理器 --经管 --SQLServer署理 --右键作业 --新建功课 --"惯例"项中输入作业称号 --"步调"项 --新建 --"步骤名"中输入步骤名 --"范例"落第择"Transact-SQL剧本(TSQL" --"数据库"决定执行饬令的数据库 --"饬令"中输入要执行的语句:execp_process --断定 --"调剂"项 --新建调剂 --"称号"中输入调整称号 --"调整范例"落第择你作业执行放置 --如果抉择"屡次泛起" --点"变动"来设置你时辰放置 并设置为自动启动,尔后将SQLAgent服务发动.不然你作业不会被执行 设置方式: 电脑--控制面板--管理对象--办事--右键 SQLSERVERA GENT--属性--策动范例--抉择"自动发动"--断定. 定时同步--3.实现同步处置惩罚的方式2. --srv1中创建如下的同步处理存储历程 creatprocp_process as --更新改削过的数据 telphone=i.telphonupdatbsetname=i.name.e authorfromsrv2.库名.dbo.authorb.i wherb.id=i.idand b.name<>i.namorb.telphon<>i.telphon --插入新增的数据 name,insertsrv2.库名.dbo.authorid.telphon name,selectid.telphonfromauthori whernotexist select*fromsrv2.库名.dbo.authorwhereid=i.id --删除已经删除的数据(如果须要的话) deletb fromsrv2.库名.dbo.authorb wherenotexist select*fromauthorwhereid=b.id go |
