SqlServer基础知识总结

1/381.回顾建库ase(MaxSize=最大空间(UNLIMITED),)(MaxSize=最大空间(UNLIMITED),)2/38必要属性----创建Xoa数据库createdatabasexoaonprimary(name='Xoa_Data',='e:\data\Xoa.mdf',=5Mb,=unlimited,)logon(='Xoa_Log',='e:\data\Xoa.ldf',=3MB,=100MB,)SizeMaxSizeSizeMaxSize4)注意事项”B”，eg：KB、MB等)3/38---2、删除数据库--练习：删除名为Xoa的数据库--检查数据库是否存在--databases：视图，存在与Master数据库中ifexists(select*fromsys.databaseswherename='xoa')dropdatabaseXoa议4.建表CharNchar4/38harunicode征]()Identity--练习：创建部门表(Department)--记录Id(deptId,int,自增)、部门名称(deptName,varchar(50),非空)createtableDepartment(deptIdintidentity(1,1),deptNamevarchar(50)notnull)5/384)数据完整性要求实现要求实现要求实现要求实现6/38规则缩写_列名分类--行内约束createtableUsers(--主键约束--主键可以由多个字段组成(复合主键)，主键列字段不能为空UserIdintidentity(1,1)primarykey,UserNamevarchar(20)notnull,--唯一约束Accountvarchar(20)notnullunique,--检查约束Pwdvarchar(20)notnullcheck(len(pwd)>=6andlen(pwd)<=20),7/38AgeAgeintnotnullcheck(age>=18andage<=60),--引用约束DeptIdintreferencesDepartment(deptId),--默认值约束Memovarchar(200)default('这个家伙很懒，什么都没留'))--方式createtableUsers(UserIdintidentity(1,1),UserNamevarchar(20)notnull,Accountvarchar(20)notnull,Pwdvarchar(20)notnull,Ageintnotnull,DeptIdint,Memovarchar(200),--约束--主键约束--primarykey(UserId),constraintpk_UserIdprimarykey(UserId),--唯一约束--unique(Account),constraintuq_Accountunique(Account),--检查约束--ck_Pwdcheck(len(pwd)>=6andlen(pwd)<=20),constraintck_Pwdcheck(len(pwd)>=6andlen(pwd)<=20),constraintck_Agecheck(age>=18andage<=60),--引用约束--foreignkey(deptId)referencesDepartment(deptId)constraintfk_DeptIdforeignkey(deptId)referencesDepartment(deptId))定义约束名定义约束名--方式createtableUsers(UserIdintidentity(1,1),UserNamevarchar(20)notnull,8/38Accountvarchar(20)notnull,Pwdvarchar(20)notnull,Ageintnotnull,DeptIdint,Memovarchar(200))go--约束altertableUsersaddconstraintpk_UserIdprimarykey(UserId)goaltertableUsersaddconstraintuq_Accountunique(Account)goaltertableUsersaddconstraintck_Pwdcheck(len(pwd)>=6andlen(pwd)<=20)goaltertableUsersaddconstraintckAge_check(age>=18andage<=60)goaltertableUsersaddconstraintfk_DeptIdforeignkey(deptId)referencesDepartment(deptId)go--方式createtableUsers(UserIdintidentity(1,1),UserNamevarchar(20)notnull,Accountvarchar(20)notnull,Pwdvarchar(20)notnull,Ageintnotnull,DeptIdint,Memovarchar(200))go--约束altertableUsersaddconstraintpk_UserIdprimarykey(UserId),constraintuq_Accountunique(Account),constraintck_Pwdcheck(len(pwd)>=6andlen(pwd)<=20),constraintck_Agecheck(age>=18andage<=60),constraintfk_DeptIdforeignkey(deptId)referencesDepartment(deptId)go9/38删除约束---修改表结构altertableUsers--删除约束dropconstraintck_age---objects:系统视图，存在于所属的数据库--判断表是否存在ifexists(select*fromsys.objectswherename='Users')droptableUsersgoSelectSelect输出内容10/38---1、输出语句--格式：select输出内容--select后可以输出任何有效内容，如：常数、变量及函数select'abc'select'中国'selectGETDATE()--格式：print输出内容--print后可以输出任何有效内容，如：常数、变量及函数print'abc'print'中国'printgetDate()3)区别4)说明和维护的，我们无法更改，只能使用。11/38@@+变量名表示没有错误@@identity@version@@servernamet---2、变量--向User表中插入数据insertintousersvalues('Mike','Mike','123456',20,1,DEFAULT)go--输出前一条语句所产生的自增序列值print@@identity--输出前一条语句所产生的错误号print@@errorinsertintousersvalues('Mike','Mike','123456',20,1,DEFAULT)--错误编号print@@error--错误编号print@@errorselect*fromusers--获取受影响的行数print@@rowcount--获取当前数据库版本号print@@version--获取当前服务器的名称print@@servername12/38部变量DeclareDeclare[(长度)]---声明局部变量--一次定义一个变量declare@i1intgo--一次性定义多个变量declare@i1int,@i2intSelectSelectSelect@变量名=值[from表名]SetSetSet@变量名=值/表达式/子查询示例---a、set语句赋值declare@i1intset@i1=20print@i1set@i1=77print@i1go--b、select语句赋值declare@i1intselect@i1=-60print@i1select@i1=90print@i113/38--c、查询并赋值--set语句declare@idint--查询语句将被视为表达式进行使用--注：使用过程中必须使用()括起来--一次只能对一个变量进行赋值set@id=(selectdeptIdfromDepartmentwheredeptName='市场部')print@idgo--select语句--一次可以对多个变量进行赋值declare@idint,@namevarchar(20)--效果等同于set语句--select@id=(selectdeptIdfromDepartmentwheredeptName='市场部')select@id=deptId,@name=deptNamefromDepartmentwheredeptName='市场部'print@idprint@namego--返回多个结果值selectdeptIdfromDepartment--set语句declare@Idint--set赋值时查询语句最多只能返回一个值set@Id=(selectdeptIdfromDepartment)print@idgo--select语句declare@Idint--set赋值时查询语句最多只能返回一个值select@Id=deptIdfromDepartmentprint@idgo--无返回值selectdeptIdfromDepartmentwheredeptName='教质部'--set语句declare@idint14/38setset@Id=-100--查询语句无返回值变量将赋值为空set@id=(selectdeptIdfromDepartmentwheredeptName='教质部')print@idgo--select语句declare@idintset@Id=-100--查询语句无返回值时将保留变量的原值select@id=deptIdfromDepartmentwheredeptName='教print@id区别【面试题】应用注意Select，赋值与查询不能同时使用15/38Convert[(长度)],表达式[,格式])执行算术运算(表达式中有一项为数值类型)执行连接运算(表达式中运算数都不为数值类型)---算术运算符print2+3--连接运算符print'a'+'c'--表达式执行按照从左向右的顺序进行执行--当表达式遇到数值类型时将执行算术"+"法运算print'1'+'7'+3--表达式包含无法转换的数据--执行过程中遇到数值类型则首先对参与运算的运算数进行转换--如果转换成功则执行算术运算，否则将抛出异常print'1'+'8'+1+'a'函数---定义变量declare@idint,@namevarchar(50)--查询并赋值select@id=deptId,@name=deptNamefromDepartment--异常：类型转换失败--print@id+@name16/38----将@id(int)转为varchar--cast()函数printcast(@idaschar(4))+@name--convert()函数printconvert(char(4),@id)+@name4.流程控制语句----6、if分支语句--a、if分支语句declare@iintset@i=20if(@i>100)print'yes'go--b、if...else分支语句declare@iintset@i=20if(@i>100)17/38printprint'yes'elseprint'no'go--c、嵌套if...else语句declare@iint,@jintset@i=20set@j=80if(@i>100)beginif(@j>150)print'if--->yes'elseprint'if--->no'endelsebeginif(@j>50)print'else--->yes'elseprint'else--->no'endgo语法示例--按照国际评分标准输出学生成绩信息--包含：学号、课程编号、成绩等级--优：-10018/38--差：低于分--注：between...and相当于>=且<=selectStudentNo,SubjectId,level=casewhen(studentresultbetween90and100)then'优'when(studentresultbetween80and89)then'良'when(studentresultbetween60and79)then'中'when(studentresultbetween0and59)then'差'else'缺考'endfromResult适用语句语法WhileWhile条件表达式示例--8、while循环--通过循环语句输出~5等个数--声明变量并赋值declare@iintset@i=1--循环输出@i的值while@i<=5beginprint@iset@i=@i+119/38endgo--横向输出个数--声明变量并赋值declare@iint,@rvarchar(100)set@i=1set@r=''while@i<=5begin--拼接字符串set@r=@r+CAST(@iasCHAR(5))set@i=@i+1end--输出拼接后的字符串print@r--为所有学生的科目Id为的课程成绩提分--每次提分，且最高成绩不能高于分--如果所有学生均已及格则结束select*fromResultwhereSubjectId=1--定义变量存储不及格学生的人数declare@countint--获取不及格学生的人数set@count=(selectCOUNT(Id)fromResultwhereSubjectId=1andStudentResult<60)--通过循环来更新不及格学生成绩while(@count>0)begin--更新所有学生成绩--条件：科目编号为且小于updateResultsetStudentResult=StudentResult+5whereSubjectId=1andStudentResult<95--重新获取不及格学生的人数set@count=(selectCOUNT(Id)fromResultwhereSubjectId=1andStudentResult<60)endprint'修改完成'20/38ggo般这个代码段称为一个执行计划。1.Select语句执行顺序1)示例代码selectGradeId,COUNT(gradeId)fromStudentwhereEmailisnotnullgroupbyGradeIdhavingCOUNT(gradeId)>20orderbyGradeId21/38顺序中中询是指将一个查询语句作为另一个语句的一部分进行使用。询在条件语句中是通过等值判断(=)使用子查询的语句就成等值子查22/38declare@namevarchar(20)set@name='杨阳'select@name,*fromResultwhereStudentNo=(selectStudentNofromStudentwhereStudentName=@name)事项4.非等值子查询select*fromresultwhereSubjectId!=(selectSubjectIdfromSubjectwhereSubjectName='走进java编程世界')5.[not]in23/38selectselectStudentNo,StudentName,DATEDIFF(year,bornDate,getDate())asage,sex,phonefromStudentwhereGradeIdin(selectGradeIdfromGradewhereGradeName='s1'orGradeName='y2')selecta.StudentNo,a.StudentName,a.Sex,b.GradeNamefromStudentaleftjoinGradebona.GradeId=b.GradeIdwhereStudentNonotin(selectStudentNofromResultwhereexamdate='2013-9-13'andSubjectIdin(selectSubjectIdfromSubjectwhereSubjectName='走进java编程世界'orSubjectName='C#语言和数据库技术'))事项➢In后的子查询只能有一个返回值列(可以有多行)6.[not]exists--编写代码更新email地址信息--如果存在emial地址为null的情况则将该生的emial地址改为"未知@"并输出存在"错误地址"--否则则输出所有所有学生的信息(学号、姓名、Email)ifexists(select*fromStudentwhereEmailisnull)beginupdateStudentsetEmail='未知@'whereEmailisnullprint'错误地址'endelseselectStudentNo,StudentName,EmailfromStudent24/38比9.应用(什么场合使用连接查询；什么时候使用子查询)事务是一个逻辑工作单元，事务中的多个操作视为一个整体，操25/38么同时失败操作的数据库系统中类通过setIMPLICIT_TRANSACTIONSon启动事务，开启后其认开启了一个新的事务(隐式事务实际中基本不被使用)。26/384)语句----业务：银行转账业务(Rose从Mike借了块钱)--转出一方(借方)：账户余额减少--转入一方(贷方)：账户余额增加--定义变量存储错误编号declare@errint=0--a、开启事务begintran--b、执行转账操作--Mike账户转出updateaccountsetblance=blance-490whereUserName='Mike'set@err=@err+@@ERRORupdateaccountsetblance=blance+490whereUserName='Rose'set@err=@err+@@ERROR--判断是否产生过错误if(@err=0)--提交事务commitelse--回滚事务rollbackgo27/38视图是一个虚拟表,视图中存储的是查询语句.使用视图时其实实在使用他的查询语句.方式view4)示例---a、定义视图createviewvw_resultas28/38selecta.StudentNo,StudentName,Sex,GradeName,d.SubjectName,StudentResult,ExamDatefromstudentaleftjoinGradebona.GradeId=b.GradeIdleftjoinResultcona.StudentNo=c.StudentNoleftjoinSubjectdonc.SubjectId=d.SubjectIdgo表或不存在引用关系的)可以像表一样进行CRUD操作➢如果视图中查询语句涉及多个表且存在引用关系时一般无法意事项➢视图可以嵌套使用，嵌套层次一般不要超过3层(效率下降)图的优点29/38索引就是对数据进行重排的一种方式,通过所有可以有效的提高数据的检索(查询)速度.teredindexOn表名(字段名)明唯一索引：创建唯一约束将自动创建唯一索引，唯一主键索引聚集索引顺序进行重排，一个数据非聚集索引：对表中数据建立索引页。一个表可以创建30/384)示例--为学生信息表的姓名创建索引createnonclusteredindexidx_studentNameonstudent(studentname)建议8)查询的列建议在较为短小的列上创建索引经常被上创建索引经常进行表连接的列上创建索引列相同值较多的不建议创建索引数据较少的列不建议创建索引31/38定期进行重建索引)查看索引--indexes：视图，存储了索引的相关信息select*fromsys.indexes--存储过程：查看相关表的索引信息sp_helpindexstudentex--5、删除索引--删除表时索引同时被删除ifexists(select*fromsys.indexeswherename='idx_studentName')dropindexstudent.idx_studentNameSql代码。在存储过程中封装了一定32/38分类：：列出当前数据系统内的所有数据库信息：列出当前数据库下所有的表/视图信息--sp_help：列出数据库内对象的信息sp_help--sp_helpindex：查看指定表的索引信息sp_helpindexstudent--查看相关表的约束信息sp_helpconstraintstudent--更改数据库名称sp_renamedb'myschool','school'--sp_databasessp_databases--sp_tablessp_tablessp_tablesgrade展存储过程33/38Xp名称3)自定义存储过程cedure@参数名类型[=默认值][output]ql4.说明34/38--a、无参存储过程--无参数只执行一个操作的存储过程createprocproc_selectasselect*fromstudentgo--c、带参数存储过程--按照学生名称查询学生成绩信息createprocproc_result--输入参数@namevarchar(20)asselecta.*fromresultaleftjoinstu