数据库常见语句

数据库常见语句--新建数据库语句createDATABASE数据库名--新建具体的数据库createdatabase数据库名on(name=数据文件名_dat,filename='存放路径',size=10)--size指的是数据文件初始大小logon(name=日志文件名_log,filename='存放路径',size=1MB--size指的是日志文件初始大小maxsize=20MB)日志文件最大为20MB--选择数据库use数据库名--新建表createtable表名(列名类型,studentidintnotnull,[no]intidentity(1,1),--标识列[name]varchar(20),pwdvarchar(20),Emailnvarchar(50))--删除列altertable表名dropcolumn列名--插入列altertable表名add列名类型插入多列ALTERTABLEstudentADDsdfaVARCHAR(20),workvarchar(10)在指定列后插入一列(该语句目前有问题)ALTERTABLEstudentADDsdfaVARCHAR(20)aftersex删除多列altertablestudentdropcolumnsdfa,work--插入一行insertinto表名(列名1,列名2,列名3,列名4)values(赋值1,赋值2,赋值3,赋值4)/*注：into可以省略,如果列名为关键字那么用[列名]，如果该列为标识列那么不能插入，直接跳过如果该列类型不是int型,那么要单引号'赋值'如果某列具有默认值时,插入该列那么用default如：insertinto表名(sex,studentid,[name],pwd,Email)values(default,168,'陈林','axjl','csdn@168')*/--例如:insertinto表名(studentid,[name],pwd,Email)values(168,'陈林','axjl','csdn@168')--插入多行1.把A表中的局部列数据插入到B表中insertintoB(B.col1,B.col2,B.col3,……)selectA.col1,A.col2,A.col3……fromA2.把A表中的数据插入到New_Table中--此New_Table表是执行查询语句时新建的，不能预先存在selectA.col1,A.col2,……intoNew_TablefromA注:如果要插入标识列的话，就这样写selectidentity(1,1)as列名,A.col……into新表formA3.通过Union合并数据插入到A表中insertA(列名1,列名2,列名3)select'','',''Unionselect'','',''Unionselect'','',''--插入多列insertstudent(studentid,[name],sex,pwd,email)select1001,'陈林','男','axjl','axjl@168'unionselect1002,'马力','女','dsa','ml@139'unionselect1003,'沪指','男','csdn','hz@168'unionselect1001,'陈浩南','男','kdn','chn@168'unionselect1001,'山脊','男','dsfg','sj@168'unionselect1001,'胡话','女','sdgas','hh@168'unionselect1001,'姜维','男','tdhe','jw@168'unionselect1001,'卡哇伊','女','fgh','kwy@168'千万要注意:default只能和有values的语句搭配--下面的一个插入语句错误insertstudent(studentid,[name],sex,pwd,email)select1001,'asdgdf',default,'axjl','axjl@168'--此语句没问题insertintostudent(studentid,[name],sex,pwd,email)values(1002,'马超',default,'dfklgjs','dfg@WSDJ')SELECT*fromstudentwhere[name]='sdf'select*fromstudentwherestudentid=168--更新数据库update表名set列名1=更新值,列名2=更新值……where条件1and条件2,……--把性别为男的全部改为默认值updatestudentsetsex=defaultwheresex='男'updatestudentset[name]='多啦A梦',sex='我是可爱多'where[name]='陈琳'and[no]=1--把A表的样式类型复制到B表中--不要内容select*intoBfromstudentwhere1<>1--删除数据delete只会删除整行，不会删除某个字段所以delete后面不能有“列名”如果有有主外键关系的话，先删外键再删主键deletefrom表名where列名=‘删除条件’truncatetable表名--注：truncate删除的是所有的行truncate不能用于有外键关系的表--truncate比delete速度快--truncate删除后再插入时标识列从0开始--delete删除后再插入时标识列继续累加--使用select查询语句select列名from表名where条件orderby排序的列名ascordesc--asc升序desc降序默认为asc1.查询全部select*from表名2.查询局部行列select列名1,列名2,列名3...from表名where条件3.在查询中使用列名查询不是“男”的学员selectcol1as编号,col2as姓名...from表名wheresex<>'男'4.查询的列合并为一列select列名1+'.'+列名2as姓名or'姓名'=列名1+'.'+列名2from表名where条件5.查询null或者notnull或者''select列名1,列名2,列名3...from表名whereemailisnulloremailisnotnulloremail=''--注意:null不等于''select*fromBwhereemailisnotnull6.在查询中使用常量列select姓名=[name],性别=sex,'湖北黄冈'as地址fromBwheresex='女'7.查询成绩前三名selecttop3列名from表名orderbyscoredesc8.按百分比%查询20%的女生的爱好selecttop20percent[name],likedfrom表名wheresex=09.查询分数降低10%后加5分还及格的人的信息按由高到低排序selectcol1as编号,(score*0.9+5)as综合成绩from表名where(score*0.9+5)>60orderbyscoredesc10.查询A表和B表，并把每张表的列合并，然后连接两张表再按降序排序selectA.col1+'.'+A.col2asnew_NamefromAunionselectB.col1+'.'+B.col2asnew_NamefromBorderbynew_Namedesc11。按多个条件排序进行查询selectcol1as编号,col2as姓名...from表名where查询条件orderby列名1,列名2字符串函数12.查询一个指定的字符串在另一个字符串中的起始位置charindex(a,b,c)a为要查询的字符串b为a所在的字符串c〔int〕为从第几个字符开始查找返回一个指定的字符串在另一个字符串中的起始位置下标起始位置为1，不是0selectcharindex('@',email,2)as下标,upper(pwd)as密码fromstudentwherelen(pwd)=3andstudentidlike'%2'13.查询字符串的长度len(a)a任意字符串select*fromstudentwherelen(pwd)>314.去除字符串left或者right的空格并连接selectRtrim('爱新觉罗')+'.'+Ltrim('守卫剑阁')as魔兽地图15.替换一个字符串中指定的所有字符replaceselectreplace(charA,'c','b')把字符串charA中的字符‘c’替换为‘b’例子:selectreplace(Rtrim('爱新觉罗')+'.'+Ltrim('守卫剑阁'),'新','心')as魔兽地图selectreplace(replace(pwd,'o','0'),'i','1')16.在一个字符串中，在指定位置删除指定长度的字符串，并在该位置插入一个新的字符串selectstuff(charA,(int)a,(int)b,charC)在字符串charA中,从b位置开始，删除长度为b的字符串，并在b位置处插入字符串charC例子:selectstuff(replace(Rtrim('爱新觉罗')+'.'+Ltrim('守卫剑阁'),'新','心'),5,1,'版')as魔兽地图17.删除字符串左边的空格Ltrim()selectLtrim('守卫剑阁')18.删除字符串右边的空格Rtrim()selectRtrim('爱新觉罗')+Ltrim('守卫剑阁')19.从字符串右边返回指定数目的字符Right()selectRight('爱新觉罗守卫剑阁',4)日期函数20.获取当天日期:Getdate()例子:selectGetdate()as日期时间21.日期时间的更改:DateAdd(YYorMMorDD,(int)a,'月/日/年')例子:selectDateAdd(YY,-10,Getdate())as十年前的日期selectDateAdd(MM,-2,Getdate())as二月前的日期selectDateAdd(DD,-2,Getdate())as二天前的日期selectDateAdd(DD,2,'02/01/1989')as二天前的日期22.两个日期的指定局部的日期区别:DateDiff(YYorMMorDD,'月a/日a/年a','月b/日b/年b')后面的减去前面的例子:selectDateDiff(YY,'12/11/1989','12/11/2008')as相隔年份selectDateDiff(MM,'12/11/2008','8/11/2008')as相隔月份selectDateDiff(DD,'12/11/2008','12/2/2008')as相隔天数23.返回星期几:DateName(dw,'01/01/2001')dw指定的日期星期几例子:selectDateName(dw,'01/01/2001')24.返回日期中指定日期局部的整数型式：DatePart()selectDatePart(day,GetDate())as日期号selectDatePart(MM,GetDate())as日期号selectDatePart(YY,GetDate())as日期号数学函数25.取绝对值：Abs()selectAbs(-45)26.取>=指定数值、表达式的最小整数：Ceiling()selectCeiling(45.56)27.取<=指定数值、表达式的最da整数：Floor()selectFloor(45.56)28.取X的Y次方:Power(x,y)selectPower(2,3)29.把一个数值表达式四舍五入为指定的精度:Round()selectRound(3.1415,3)30.判断数字为正数负数还是为0正数返回+1负数返回-10返回0Sign()selectSign(-8)31.取浮点表达式的平方根:Sqrt()selectSqrt(9)系统函数32.转换数据类型Convert(转换类型,要转换的数据)selectConvert(int,DateName(YY,GetDate()))+Convert(int,DateName(MM,GetDate()))as当年加当天33.返回指定表达式的字节数Datalength()selectDatalength('sdfalksd')selectCurrent_Useras你登陆的用户名selectHost_Name()as电脑名selectSystem_Useras当前所登录的用户名称selectUser_Name(1)as从给定的用户id返回用户名特殊排序先按“-”前半局部倒序再按"-"的后半局部倒序selectnumas级别,姓名=[name]fromstudentorderbyconvert(int,left(num,charindex('-',num)-1))desc,convert(int,ltrim(stuff(num,1,charindex('-',num),'')))desc34.like的妙用例子：select*fromstudentswherenameslike'胡%'andlikednotlike'%S'35.between的妙用select*from表名wherescorenotbetween60and8036.in的妙用select*from表名where列名in('A','B','C')orderby列名(ascordesc)聚合函数1.求和函数:sum()selectsum(列名)from表名where条件2.求平均数函数:avg()selectavg(列名)from表名where条件3.求最大值Max最小值MinselectMax(列名)as最高分,Min(列名)as最低分from表名where条件4.统计函数:count()selectcount(表达式or列名or*)from表名where条件selectcount(age)fromstudentswhereage>225.分组查询:groupby(此语句在select中所指定的列有限)select列名,与分组相对应的列from表名groupby列名例子:selectcourseid,avg(score)as课程平均分fromscoregroupbycourseidselectstudentidas学员编号,courseidas内部测试,avg(score)as内部测试平均分from表名groupbystudentid,courseid6.Having的妙用要求：查询补考过的学员的平均分selectstudentidas学员编号,courseidas内部测试,avg(score)as内部测试平均分from表名groupbystudentid,courseidHavingcount(score)>17.wheregroupbyhaving在查询语句中的次序例子:selectstudentid,courseid，avg(score)wherescore>60groupbystudentid,courseidHavingcount(score)>1select部门编号,count(*)form员工信息表where工资>=2000groupby部门编号havingcount(*)>18.多表连接1.内连接：innerJoina.在where子句中指定连接条件(A表B表)selectA.列名,B.列名fromA,BwhereA.col=B.colb.在from子句中使用Join...inselectA.列名,B.列名from表1asAinnerJoin表2asBon(A.col=B.col)selectA.列名,B.列名from表1asAinnerJoin表2asBon(A.col=B.col)where条件c.多表外连接selectA.列名,B.列名,C.列名from表1asAinnerJoin表2asBon(A.col=B.col)innerJoin表3asCon(C.col=B.col)2.外连接a.左连接：leftJoinselectA.列名,B.列名from表1asAleftJoin表2asBon(A.col=B.col)b.右连接：rightJoinselectA.列名,B.列名from表1asArightJoin表2asBon(A.col=B.col)c.全连接：fullJoinselectA.列名,B.列名from表1asAfullJoin表2asBon(A.col=B.col)3.交叉连接：crossJoin〔行数=A列*B列〕selectA.列名,B.列名from表1asAcrossJoin表2asBon(A.col=B.col)9.查询部门名selectdistinctbumenIdas部门名注意:distinct是不同的意思，如果没distinct的话，那么查询结果会出现重复的部门名查询一张表中的基数行和偶数行表名:tbl字段名：A主键字段:IDKEY(标识列，标识种子:1,标识增长量:1)selectA,identity(int,1,1)asIDintotemtablefromtblselectsum(A)as奇数列汇总fromtemtablewhereID%2<>0selectsum(A)as奇数列汇总fromtemtablewhereID%2=0银行卡恢复Users表〔主表〕CardIDUserName16张三23陈林25马力34胡志36老杜Account表〔子表〕CountIDCardIDScore1163522316833614525马力子表中没有要求插入这两条记录Score为234胡志insertintoAccount(Account.CardID,Account.Score)selectUsers.CardID,2fromUsersleftjoinAccounton(Account.CardID=Users.CardID)whereAccount.CardIDisnull在指定列的后面插入一列方法步骤：(原表stu已存在)1.新建一张列序即类型为用户所要求的内容为空的表sut3createtablestu3(numberintidentity(1,1),namesvarchar(20)notnull,ageint,sexbitdefault1,likednvarchar(20))2.将原表的内容插入到新建表stu3中insertintostu3(names,sex,liked)selects,stu.sex,stu.likedfromstu3.删除原有表studroptablestu4.把stu3表中的信息插入到新建的表stu中selectidentity(int,1,1)asnumber,s,stu3.age,stu3.sex,stu3.likedintostufromstu35.查看结果select*fromstu数据库设计和高级查询表的标准设计：必须满足三大范式1.原子性〔不可再分〕2.非主键字段必须全部依赖于主键3.非主键不能传递依赖--如何检测是否存在数据库stuDB，如果存在，先删除，再创立--SQLServer将数据库的清单存放在master系统数据库的sysdatabases表中usemaster--设置当前数据库为master，以便访问sysdatabases表goifexists(select*fromsysdatabaseswherename='stuDB')dropdatabasestuDBcreatedatabasestuDBonprimary(name='stuDB_data',filename='D:\project\stuDB_data.mdf',size=5mb,maxsize=100mb,filegrowth=15%)logon(name='stuDB_log',filename='D:\project\stuDB_log.ldf',size=2MB,filegrowth=1MB)go--创立学员信息表stuInfocreatetablestuINfo(stunamevarchar(20)notnull,stuNochar(6)notnull,stuAgeintnotnull,stuIDnumeric(18,0),stuSeatsmallintidentity(1,1),stuAddresstext)go--创立学员成绩表stuMarksusestuDBgoifexists(select*fromsysobjectswherename='stuInfo')droptablestuInfocreatetablestuMarks(ExamNochar(7)notnull,stuNoschar(6)notnull,writenExamintnotnull,LabExamintnotnull)go--删除学员信息表stuInfodroptablestuInfo--添加主键约束〔将stuNo作为主键〕altertablestuInfoaddconstraintpk_stuNoprimarykey(stuNo)--添加唯一约束altertablestuInfoaddconstraintUQ_stuIDunique(stuID)--添加默认约束altertablestuInfoaddconstraintDF_stuAddressdefault('地址不详')forstuAddress--添加检查约束altertablestuInfoaddconstraintCK_stuAgecheck(stuAgebetween15and40)--添加外键约束〔主表stuInfo和从表stuMarks建立关系，关联字段为stuNo〕altertablestuMarksaddconstraintFK_stuNoforeignkey(stuNo)referencesstuInfo(stuNos)go--查看表的约束是否存在execsp_helpconstraint表名execsp_helpconstraintstuMarks--删除约束altertable表名dropconstraint约束名--例如:altertablestuInfodropconstraintDF_stuAddress--创立登陆账户--添加windows登陆账户execsp_grantlogin'计算机名\计算机登录名'execsp_grantlogin'HGACCP-1XL60HQ0\Administrator'--添加SQL登陆账户execsp_addlogin'账户名','密码'execsp_addlogin'chenlin','axjl@168'go--创立数据库用户usestuDBgoexecsp_grantdbaccess'HGACCP-1XL60HQ0\Administrator','AdministratorDBUser'execsp_grantdbaccess'chenlin','chenlinDBUser'--给数据库用户授权grant权限[on表名]to数据库名--给用户chenlinDBUser授予建表的权限usestuDBgrantcreatetabletochenlinDBUser--给用户chenlinDBUser授予增删查改的权限grantselect,insert,update,deleteonstuMarkstochenlinDBUser--删除用户对表的权限revoke权限名on表名to用户名例如:revokeselectonstuMarkstochenlinDBUser--删除数据库用户execsp_revokedbaccess用户名局部变量的声明declare@变量名数据类型例如:declare@namevarchar(8)局部变量的赋值set@name=值或者select@name=值(注意,select语句赋值要求该语句返回信息不能多于一条,否那么将把最后一条信息赋值给变量)实例1:查找李文才的左右同桌declare@namevarchar(8)set@name='李文才'select*fromstuInfowherestuName=@namedeclare@seatintselect@seat=stuSeatfromstuInfowherestuName=@nameselect*fromstuInfowhere(stuSeat=@seat+1)or(stuSeat=@seat-1)go全局变量@@error--最后一个T-SQL错误的错误号@@identity--最后一次插入的标识列@@language--当前使用的语言@@max_connections--可以新建的同时连接的最大数目@@rowcount--受上一个SQL语句影响的行数@@servername--本地效劳器的名称@@servicename--该计算机上的SQL效劳器的名称@@timeticks--当前计算机上没每刻度的微秒数@@trancount--当前连接翻开的事物数@@version--SQLServer的版本信息输出语句print局部变量or字符串select局部变量as自定义列名例如：print'效劳器的名称:'+@@servernameprint'当前错误号'+convert(varchar(5),@@error)select@@servernameas'效劳器的名称'1if-else条件语句if(条件)begin语句1语句2endelse...insertintostuMarks(ExamNo,stuNo,writenExam,LabExam)select's271811','s25303',80,58unionselect's271813','s25302',50,90unionselect's271815','s25302',65,0unionselect's271816','s25301',77,82insertintostuInfo(stuname,stuNo,stuSex,stuAge,stuAddress)select'张秋丽','s25301','男',18,'北京海淀'unionselect'李文才','s25302','男',31,'地址不详'unionselect'李思文','s25303','女',22,'河南洛阳'unionselect'欧阳俊雄','s25304','男',28,'新疆'altertablestuInfodropconstraintUQ_stuIDaltertablestuInfodropcolumnstuIDselect*fromstuInfoselect*fromstuMarks例子：统计并显示本班笔试平均分，70以上，显示”成绩优秀“，并显示前3名学员的考试信息如果70以下，显示“成绩较差”，并显示后三名学员的考试信息declare@avgfloatselect@avg=avg(writenExam)fromstuMarksprint'本班平均分:'+convert(varchar(5),@avg)if(@avg>70)beginprint'本班笔试成绩优秀，前三名的成绩为'selecttop3*fromstuMarksorderbywritenExamdescendelsebeginprint'本班笔试成绩较差，后三名的成绩为'selecttop3*fromstuMarksorderbywritenExamend2while循环语句while(条件)语句或语句块[break]例子:统计不及格的人数，如果有人不及格，那么加2分循环判断insertintostuMarks(examNo,stuNo,writenExam,LabExam)values('S271819','S25318',56,48)select*fromstuMarksdeclare@nintwhile(1=1)beginselect@n=count(*)fromstuMarkswherewritenExam<60if(@n>0)updatestuMarkssetwritenExam=writenExam+2elsebreakendprint'加分后的成绩如下:'select*fromstuMarks3case多分支语句(case语句中没有begin,只有end)casewhen条件1then结果1when条件2then结果2else其他结果end例子:采用ABCDE五级打分制来显示笔试成绩1.A级>=902.B级80~893.C级70~794.D级60~695.E级<60select*fromstuMarksprint'ABCDE五级显示成绩如下:'selectstuNo,成绩=casewhenwritenExam<60then'E'whenwritenExambetween60and69then'D'whenwritenExambetween70and79then'C'whenwritenExambetween80and89then'B'else'A'endfromstuMarks批处理:go注意:SQLServer规定：建库建表存储过程视图都必须在末尾添加"GO"简单子查询例子：１.查询年龄比”李斯文“大的学员，要求显示这些学员的信息方法1：declare@ageintselect@age=stuAgefromstuINfowherestuName='李思文'select*fromstuInfowherestuAge>@ageGO方法2：select*fromstuINfowherestuAge>(selectstuAgefromstuINfowherestuName='李思文')注意:将子查询和比拟运算符联合使用，必须保证子查询返回的值不能多于一个例子：２.查询笔试刚好通过60分的学员名单方法1：selectstuNamefromstuInfoinnerjoinstuMarksonstuInfo.stuNo=stuMarks.stuNowherewritenExam=92GO方法２：selectstuNamefromstuInfowherestuNo=(selectstuNofromstuMarkswherewritenExam=92)GOin和notin子查询要查询笔试成绩刚好为98分的学员〔而98分的学员又不只一个〕selectstuNamefromstuInfowherestuNoin(selectstuNofromstuMarkswherewritenExam=98)GO查询参加考试的学员名单selectstuNamefromstuInfowherestuNoin(selectstuNofromstuMarks)GO查询没参加考试的学员名单selectstuNamefromstuInfowherestuNonotin(selectstuNofromstuMarks)GOdeletefromstuMarkswhereLabExam=48andwritenExam<>88exists和notexists字查询ifexists(子查询)语句例子：如果有人笔试到达80分以上，那么每人加2分，否那么每人加5分ifexists(select*fromstuMarkswherewritenExam>80)beginprint'本班有人笔试成绩高于80分，每人加2分，加分后的成绩为'updatestuMarkssetwritenExam=writenExam+2select*fromstuMarksendelsebeginprint'本班没人笔试成绩高于80分，每人加5分，加分后的成绩为'updatestuMarkssetwritenExam=writenExam+5select*fromstuMarksendGOnotexists子查询ifnotexists(select*fromstuMarkswherewritenExam>60andlabExam>60)beginprint'本班无一人通过考试，试题偏难，每人加3分，加分后的成绩为:'updatestuMarkssetwritenExam=writenExam+3,labExam=labExam+3select*fromstuMarksendelsebeginprint'本班考试成绩一般，每人只加1分，加分后的成绩为:'updatestuMarkssetwritenExam=writenExam+1,labExam=labExam+1select*fromstuMarksendGO综合练习1.统计本次考试的缺考情况select应到人数=(selectcount(*)fromstuInfo),实到人数=(selectcount(*)fromstuMarks),缺考人数=((selectcount(*)fromstuInfo)-(selectcount(*)fromstuMarks))2.提取学员的成绩信息并保存结果，包括姓名学号笔试机试是否通过ifexists(select*fromsysobjectswherename='newTable')droptablenewTableselectstuName,stuInfo.stuNo,writenExam,LabExam,isPass=casewhenwritenExam>=60andlabExam>=60then1else0endintonewTablefromstuInfoleftjoinstuMarksonstuInfo.stuNo=stuMarks.stuNo3.比拟笔试平均分和机试平均分，较低分的进行循环加分，但提分后最高分不能高于97分declare@avgWritennumeric(4,1),@avgLabnumeric(4,1)select@avgWriten=avg(writenExam)fromnewTablewherewritenExamisnotnullselect@avgLab=avg(labExam)fromnewTablewherelabExamisnotnullif@avgWriten<@avgLabwhile(1=1)beginupdatenewTablesetwritenExam=writenExam+1if(selectmax(writenExam)fromnewTable)>=97breakendelsewhile(1=1)beginupdatenewTablesetlabExam=labExam+1if(selectmax(labExam)fromnewTable)>=97breakendupdatenewTablesetisPass=casewhenwritenExam>=60andlabExam>=60then1else0end--select*fromnewTable4.提分后，统计学员的成绩和通过情况select姓名=stuName,学号=stuNo,笔试成绩=casewhenwritenExamisnullthen'缺考'elseconvert(varchar(5),writenExam)end,机试成绩=casewhenlabExamisnullthen'缺考'elseconvert(varchar(5),labExam)end,是否通过=casewhenisPass=1then'是'else'否'endfromnewTable5.提分后统计学员的通过率情况select总人数=count(*),通过人数=sum(isPass),通过率=(convert(varchar(5),avg(isPass*100))+'%')fromnewTable事务事务四大特性:1.原子性:事务中有一处失败，整个事务失败。2.一致性:事务处理前后都必须一致。3.隔离性:事务之间必须相互独立，不能有依赖。4.持久性:事务处理后，他对系统的影响是永久的。T-SQL事务语句开始事务:begintransaction提交事务:committransaction回滚〔撤销〕事务:rollbacktransaction实例1/*----------------创立表--------------------*/usestuDBGOifexists(select*fromsysobjectswherename='bank')droptablebankGOcreatetablebank(customerNamechar(10),--顾客姓名currentMoneyMoney--当前余额)Goaltertablebank--添加约束，账户余额必须>=1AddconstraintCK_currentMoneycheck(currentMoney>=1)GOinsertintobank(customerName,currentMoney)values('张三',1000)insertintobank(customerName,currentMoney)values('李四',1)select*frombankGo实例2将张三的钱1000块转到李四账户updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='张三'updatebanksetcurrentMoney=currentMoney+1000wherecustomerName='李四'GOselect*frombankGO实例3usestuDBGO--恢复原来的数据updatebanksetcurrentMoney=currentMoney-1000wherecustomerName='李四'setnocounton--不显示受影响的行数信息print'查看转账事务钱的余额'select*frombankGO/*--开始事务〔指定事务从此处开始，后续的T-SQL语句都是一个整体〕--*/begintransaction/*--定义变量，用于累计事务执行过程中的错误--*/declare@erroeSumintset@erroeSum=0--初始化为0，即无错误/*--转账:张三的账户少1000块，李四的账户多1000块--*/updatebanksetcurrentMoney=currentMoney-800wherecustomerName='张三'set@erroeSum=@erroeSum+@@error--累计是否有错误updatebanksetcurrentMoney=currentMoney+800wherecustomerName='李四'set@erroeSum=@erroeSum+@@error--累计是否有错误print'查看转账事务过程中的余额'select*frombank/*--根据是否有错误，确定事务提交还是撤销--*/if@erroeSum<>0--如果有错误beginprint'交易失败,回滚事务'rollbacktransactionendelsebeginprint'交易成功,提交事务，写入硬盘，永久保存'committransactionendGOprint'查看转账事务后的余额'select*frombankGO索引索引分类：1.唯一索引:唯一索引要求两行不能有相同的索引值。2.逐渐索引:给表定义一个主键将自动创立主键索引，主键索引是唯一索引的特殊类型。主键索引中的每个值都必须唯一。2.聚集索引:聚集索引中，表中各行的物理顺序与键值的逻辑〔索引〕顺序相同，每张表里面只能有一个聚集索引。〔如果是非聚集索引，表中各行的物理顺序与键值顺序不匹配〕注意:一个表中只能创立一个聚集索引，但可以有多个非聚集索引，设置某列为主键，该列就默认为聚集索引。创立索引语法:create[unique][clustered|nonclustered]indexindex_nameontable_name(column_name[,column_name2]...)[withfillfactor=x]其中:unique指定唯一索引，可选。clustered、nonclustered指定是聚集索引还是非聚集索引，可选。fillfactor表示填充因子，指定一个0~~100的值，该值指示索引页填满的空间所占的百分比。实例:usestuDBGO/*--检测是否存在索引(索引存放在系统表sysindexes中)--*/ifexists(selectnamefromsysindexeswherename='ix_stuMarks_writtenExam')dropindexstuMarks.ix_stuMarks_writtenExam--删除索引/*--笔试列创立非聚集索引，填充因子为30%--*/createnonclusteredindexix_stuMarks_writtenExamonstuMarks(writtenExam)withfillfactor=30GO实例:/*--指定按索引:ix_stuMarks_writtenExam查询--*/select*fromstuMarks(index=ix_stuMarks_writtenExam)or(index(ix_stuMarks_writtenExam))wherewrittenExambetween60and90视图创立视图语句:createviewview_nameas<select语句>实例：usestuDBGO/*--检测是否存在:试图存放在系统表sysobjects中--*/ifexists(select*fromsysobjectswherename='view_stuInfo_stuMarks')dropviewview_stuInfo_stuMarksGO/*--创立视图:查看学员的成绩情况--*/createviewview_stuInfo_stuMarksasselect姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,机试成绩=labExam,平均分=(weittenExam+labExam)/2fromstuInfoleftjoinstuMarksonstuInfo.stuNo=stuMarks.stuNoGO/*--使用视图:视图是一个虚拟表，可以像物理表一样翻开--*/select*fromview_stuInfo_stuMarks存储过程〔存放在sysobjects中〕存储过程的几大优点:1.允许模块化程序设计〔只需创见一次存储过程并将其存储在数据库中，屡次调用〕2.执行速度比T-SQL批处理更快3.减少网络流量4.可作为平安机制使用存储过程分类：1.系统存储过程2.自定义存储过程常见的系统存储过程sp_databases列出效劳器上的所有数据库sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns返回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程sp_password添加或修改登录账户的密码sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本实例--Purpose:常用系统存储过程的使用execsp_databases--列出当前系统中的数据库execsp_renamedb'old_DB','new_DB'--更改数据库的名称usestuDBGOexecsp_tables--当前数据库可查询的对象的列表execsp_columns表名--查看某个表列的信息execsp_help表名--查看某个表所有信息execsp_helpconstraint表名--查看某个表的约束execsp_helpindex表名--查看某个表的索引execsp_helptext'视图名'--查看视图的语句文本execsp_stored_procedures--返回当前数据库中的所有存储过程列表新建文件夹、列出文件列表〔必须启动SQL的外围配置器〕execxp_cmdshellDOS命令[NO_output]实例usemasterGO/*--创立数据库bankDB,要求保存在D:\bank--*/execxp_cmdshell'mkdirD:\bank',NO_output--创立文件夹D:\bank--创立库bankDBifexists(select*fromsysdatabaseswherename='bankDB')dropdatabasebankDBGOcreatedatabasebankDBon(name='bankDB_data',filename='D:\bank\bankDB_data.mdf',size=3MB,filegrowth=15%)logon(name='bankDB_log',filename='D:\bank\bankDB_log.ldf',size=3MB,filegrowth=15%)GOexecxp_cmdshell'dirD:\bank\'--查看文件自定义存储过程创立不带参数存储过程语句:createproc[edure]存储过程名[{@参数1数据类型}[=默认值][output],......{@参数1数据类型}[=默认值][output]]asSQL语句实例usestuDBGO/*--检测是否存在，存储过程存放在sysobjects中--*/ifexists(select*fromsysobjectswherename='proc_stu')dropprocedureproc_stuGO/*--创立存储过程--*/createprocedureproc_stuasdeclare@writtenAvgfloat,@labAvgfloat--笔试平均分和机试平均分变量select@writtenAvg=avg(writtenExam),@labAvg=avg(labExam)fromstuMarksprint'笔试平均分:'+convert(varchar(5),@writtenAvg)print'机试平均分:'+convert(varchar(5),@labAvg)if(@writtenAvg>70and@labAvg>70)print'本班考试成绩:优秀'elseprint'本班考试成绩:较差'print'--------------------------------------------------------------------------'selectstuName,stuInfo.stuNo,writtenExam,labExamfromstuInfoinnerjoinstuMarksonstuInfo.stuNo=stuMarks.stuNowherewrittenExam<60orlabExam<60GO/*--调用执行存储过程--*/execproc_stu--调用存储过程的语法:exec过程名[参数]创立带参数存储过程语句:createproc[edure]存储过程名{@参数1数据类型}[=默认值][output],......{@参数1数据类型}[=默认值][output]asSQL语句实例usestuDBGO/*--检测是否存在，存储过程存放在sysobjects中--*/ifexists(select*fromsysobjectswherename='proc_stu')dropprocedureproc_stuGO/*--创立存储过程--*/createprocedureproc_stu@writtenPassint,--输入参数:笔试及格线@labPassint--输入参数:机试及格线asprint'--------------------------------------------------------------------------'print'参加本次考试没有通过的学员'selectstuName,stuInfo.stuNo,writtenExam,labExamfromstuInfoinnerjoinstuMarksonstuInfo.stuNo=stuMarks.stuNowherewrittenExam<@writtenPassorlabExam<@labPassGO/*--调用本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分--*/execproc_stu60,55--或这样调用:execproc_stu@labPass=55,@writtenPass=60/*---调用存储过程---execproc_stu---都采用默认值:笔试和机试及格线都为60分execproc_stu64---机试采用默认值，笔试及格线64，机试60分execproc_stu60,65---都不采用默认值，笔试及格线60，机试及格线65--错误的调用方法:execproc_stu,55---希望笔试采用默认值，机试及格线为55--正确的调用方式:execproc_stu@labPass=55---笔试采用默认值，机试及格线为55---*/实例usestuDBGO/*--检查是否存在:存储过程存放在系统表sysobjects中--*/ifexists(select*fromsysobjectswherename='proc_stu')dro