还剩53页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
语句建库、表回顾
1.数据库文件1>主数据文件.mdf>次数据文件.ndf>日志文件・ldf建库
2.语法1Create DataBase数据库名称On primaryName=逻辑名称,,二物理名称,Size二初始大小单位KB、MB、GB、TB,MaxSize二最大空间UNLIMITED,增长速度以自定义约束名>外部约束一方式createtableUsersUseridintidentity1,1,UserNamevarchar20notnull,Accountvarchar20notnull,Pwdvarchar20notnull,Ageintnotnull,Deptldint,Memovarchar200go一约束altertableUsersaddconstraintpk_UserIdprimarykey UseridgoaltertableUsersaddconstraintuq_Accountunique AccountgoaltertableUsersaddconstraintck_Pwdchecklenpwd=6andlenpwd=20goaltertableUsersaddconstraintck_Agecheckage=18andage=60goaltertableUsersaddconstraintfk_DeptIdforeignkeydeptldreferencesDepart mentdeptldgo一方式createtableUsersUserldintidentity1,1,UserNamevarchar20notnull,Accountvarchar20notnull,Pwdvarchar20notnull,Ageintnotnull,Deptldint,Memovarchar200go一约束altertableUsers addconstraintpk_UserIdprimarykeyUserid,constraintuqAccountuniqueAccount,constraintck_Pwdchecklenpwd=6andlenpwd=20,constraintck Agecheckage=18andage=60,constraintfk_DeptIdforeignkeydeptldreferencesDepartmentd eptldgo方式
1、2适用于建表时已经设计好约束时运用;方式
3、4适用于在表建立后追加约束时运用删除约束8语法Drop constraint约束名示例一修改表结构altertableUsers一删除约束dropconstraintckage删除数据表
5.)语法1Drop table表名称)示例2一objects:系统视图,存在于所属的数据库一推断表是否存在ifexists(select*fromsys.objectswherename=,Users)droptableUsersgo
二、编程Sql输出语句
1.)语句1SelectSelect输出内容PrintPrint输出内容)示例2--
1、输出语句一格式select输出内容一select后可以输出任何有效内容,如常数、变量及函数select abcselect,中国selectGETDATEO一格式print输出内容一print后可以输出任何有效内容,如常数、变量及函数print abcprint中国’printgetDate())区分3Select在网格中对数据进行显示;Print在消息窗口中对数据进行显示Select支持列别名;print不支持列别名)说明4输出的内容可以是变量、常数、函数或表达式变量
2.全局变量1>特点全局变量是由系统定义和维护的,我们无法更改,只能运用>命名规则@@+变量名>常用全局变量令@@error获得前一条语句执行过程中的错误编号,0表示没有错误@@identity获得前一条语句所产生的自增列的值令@@version获得当前数据库版本号今@@servername获得当前服务器的名称o@@rowcount获得受影响的行数>示例--
2、变量一向User表中插入数据insertintousersvaluesMike,Mike,123456,,20,1,DEFAULTgo一输出前一条语句所产生的自增序列值print@@identity一输出前一条语句所产生的错误号print@@errorinsertintousersvaluesMike,‘Mike,123456,20,1,DEFAULT一错误编号print@@error一错误编号print@@errorselect*fromusers一获得受影响的行数print@@rowcount一获得当前数据库版本号print@@version一获得当前服务器的名称print@@servername局部变量2>特点局部变量是由用户自定义的,局部变量的定义遵循先定义后赋值的原则>语法Declare变量名变量类型[(长度)]命名规则@+变量名>示例一声明局部变量一—次定义一个变量declare@ilintgo一—次性定义多个变量declare@ilint,@i2int>赋值语句令SelectSelect@变量名=值[from表名]令SetSet@变量名二值/表达式/子查询今示例--a>set语句赋值declare@ilintset@il=20print@ilset@il=77print@ilgo--b、select语句赋值declare@ilintselect@il=-60print@ilselect@il=90print@il一c、查询并赋值--set语句declare@idint一查询语句将被视为表达式进行运用一注运用过程中必需运用括起来一—次只能对一个变量进行赋值set@id=selectdeptldfromDepartmentwheredeptName二市场部print@idgo--select语句一—次可以对多个变量进行赋值declare@idint,@namevarchar20一效果等同于set语句一一select@id=select deptldfrom DepartmentwheredeptName=市场部select@id=deptld,@name=deptNamefromDepartmentwheredeptName=,市场部print@idprint@namego一返回多个结果值selectdeptldfromDepartment--set语句declare@Idint一set赋值时查询语句最多只能返回一个值set@Id=selectdeptldfromDepartmentprint@idgo--select语句declare@Idint一set赋值时查询语句最多只能返回一个值select@Id=deptIdfromDepartmentprint@id goLogonName二逻辑名称,二物理名称,Size二初始大小单位KB、MB、GB、TB,MaxSize二最大空间UNLIMITED,增长速度必要属性2>存储位置>初始大小>增长速度示例3一创建Xoa数据库一数据文件初始大小为MB,增长速度为虬无最大上限一日志文件初始大小为MB,增长速度为MB最大上限为MBcreatedatabasexoaonprimaryname=Xoa_Data,一无返回值select deptIdfromDepartmentwheredeptName=,教质部,--set语句declare@idintset@Id=-100一查询语句无返回值变量将赋值为空set@id=selectdeptldfromDepartmentwheredeptName=教质部print@idgo--select语句declare@idintset@Id=-100一查询语句无返回值时将保留变量的原值select@id=deptIdfromDepartmentwheredeptName=,教质部’print@id令区分【面试题】/Set语句一次只能对一个变量进行赋值;select语句一次可以对多个变量进行赋值/Set赋值时查询将做为条件表达式;select语句查询时可以将变量定义在语句内/Set赋值时查询语句最多只能有一个返回值;select赋值时查询语句可以有多个返回值,但是只有最终一个赋值给变量/查询赋值时,假如查询语句无返回结果时,set将为变量赋值为null;select将保留原值应用多变量赋值时考虑运用select;单一赋值时考虑seto今留意Select赋值时,赋值及查询不能同时运用Set赋值效率偏高;select赋值效率偏低转换函数
3.)“+”运算符1>作用令执行算术运算(表达式中有一项为数值类型)令执行连接运算(表达式中运算数都不为数值类型)>示例一算术运算符print2+3一连接运算符print,a+,c一表达式执行依据从左向右的依次进行执行一当表达式遇到数值类型时将执行算术、〃法运算print]+,T+3一表达式包含无法转换的数据一执行过程中遇到数值类型则首先对参及运算的运算数进行转换一假如转换成功则执行算术运算,否则将抛出异样print r+,8+1+a算术运算高于连接运算)转换函数2CastCast(表达式as目标类型[(长度)])ConvertConvert(目标类型[(长度)]表达式[,格式])示例--定义变量declare@idint,@namevarchar50一查询并赋值select@id=deptld,@name=deptNamefromDepartment-异样类型转换失败-—print@id+@name--将防1int转为varchar--cast函数print cast@id aschar4+@name--convert函数printconvert char⑷,@id+@name Convert及cast【面试题】Convert转换过程中可以设置转换的格式;cast无法设置转换格式流程限制语句
4.分支语句1If•••else语句If条件表达式Begin代码段1EndElseBegin代码段2End说明sql中运用begin-end替代{},只有一条语句的代码段可以省略begin…end示例--
6、if分支语句--a if分支语句declare@iintset@i=20if@i100print yesgo一b、if...else分支语句declare@iintset@i=20if@i100print yeselseprintnogo--c、嵌套if...else语句declare@iint,@jintset@i=20set@j=80beginif@j150print if〉yeselseprint if〉noendelsebeginif@j50print else〉yeselseprint else〉noendgo Case语句令语法CaseWhen条件1then值表达式1,[,else值表达式n End令示例一依据国际评分标准输出学生成果信息一包含学号、课程编号、成果等级一优TOO一良-90--中-80一差低于分一注between...and相当于>二且〈二selectStudentNo,Subjectld,level=casewhen studentresultbetween90and100then优when studentresultbetween80and89then良’when studentresultbetween60and79then‘中’when studentresultbetween0and59then差’else缺考’endfromResult令适用/表中数据结果值的转换,如示例/表结构的转换,如竖表转横表或横表转竖表)循环语句2While语句令语法While条件表达式Begin循环体;[break/continue]end令示例一
8、while循环—通过循环语句输出5等个数〜一声明变量并赋值declare@iintset@i=1--循环输出铀的值while@i=5beginprint@iset@i=@i+lendgo一横向输出个数一声明变量并赋值declare@iint,@rvarchar100set@i=1set@r=,while@i=5begin一拼接字符串set@r=@r+CAST@iasCHAR5set@i=@i+lend一输出拼接后的字符串print@r一为全部学生的科目Id为的课程成果提分一每次提分,且最高成果不能高于分一假如全部学生均已及格则结束select*fromResultwhereSubjectld=l一定义变量存储不及格学生的人数declare@countint一获得不及格学生的人数set@count=selectCOUNTId fromResultwhereSubjectId=landStudentResult60一通过循环来更新不及格学生成果while@count0begin一更新全部学生成果一条件科目编号为且小于updateResultsetStudentResult=StudentResult+5whereSubjectId=l andStudentResult95一重新获得不及格学生的人数set@count=selectCOUNTIdfromResultwhereSubjectId=landStudentResult60endprint修改完成’go令Break及ContinueBreak用于终止当前循环的执行;continue用于终止本次循环,起先下次循环二e:\data\Xoa.mdf,Size=5Mb,MaxSize=unlimited,二15%logonname=Xoa_Log,二e:\data\Xoa.Idf,Size=3MB,MaxSize=100MB,=1MB意事项4>单位必需完整(必需具有”B,egKB、MB等)>数据库的逻辑名称不能出现重复)建议5>将数据文件和日志文件进行分开存放>数据库的初始大小及增长速度计算后在定义
5.go>作用将当前g语句以前的全部代码(到上一个g语句止)视为一个代码段进行编译执行一般这个代码段称为一个执行支配Go语句也被称为批处理语句>优势批处理语句可以提高代码的执行效率>运用一般将一组或一个业务定义为一个执行支配
三、子查询语句执行依次
1.Select)示例代码1selectGradeld,COUNTgradeld fromStudentwhereEmai1isnotnullgroupbyGradeldhavingCOUNTgradeld20orderbyGradeld)执行依次2From从原始表中抓取全部数据并且存储一个虚拟表中Where将虚拟表中所产生数据执行条件处理并且筛选出新数据存储到其次个虚拟表中Group by将虚拟表2中的数据进行分组且存储到虚拟表3中Having将虚拟表3中的数据进行筛选并存储到虚拟表4中Select从虚拟表4中抓取全部数据并且存储到虚拟表5中(限制列的数量、列别名等)Order by对虚拟表5中的数据进行排序并且存储到新的虚拟表中概述
2.所谓子查询是指将一个查询语句作为另一个语句的一部分进行运用那么这个查询语句就称为子查询子查询可以用在赋值、查询、修改及删等操作语句中后三项一般作为条件进行运用等值子查询
3.)概述1在条件语句中是通过等值推断
(二)运用子查询的语句就成等值子查询)示例2declare@namevarchar20set@name=,杨阳’select@name,*fromResultwhereStudentNo=selectStudentNofromStudentwhereStudentName=意事项3©name等值子查询中,子查询只能返回单一的结果值非等值子查询)概述
4.1所谓非等值子查询是指不是运用“二”运算符进行连接的查询语句)示例2select*fromresultwhereSubjectId!=selectSubjectldfromSubjectwhereSubjectName二走进java编程世界
5.[not]in运用1当子查询中返回一组多个值时可以运用[not]in运算符进行连接示例2selectStudentNo,StudentName,DATEDIFFyear,bornDate,getDateasage,sex,phonefromStudentwhereGradeldinseiectGradeldfromGradewhereGradeName=,si orGradeName=,y2selecta.StudentNo,a.StudentName,a.Sex,b.GradeNamefromStudentaleftjoinGradebona.Gradeld=b.GradeldwhereStudentNonotinselectStudentNofromResultwhereexamdate=,2023-9-13andSubjectldinselectSubjectldfromSubjectwhereSubjectName=,走进java编程世界orSubjectName=,C#语言和数意事项3据库技术In后的子查询只能有一个返回值列(可以有多行)
6.[not]exists)概述1Exists的返回值为布尔值,可以理解为是否有数据行存在一般可以用来作为推断运用)示例2一编写代码更新email地址信息一假如存在emial地址为null的状况则将该生的emial地址改为〃未知@〃并输出存在〃错误地址〃一否则则输出全部全部学生的信息(学号、姓名、Email)ifexists(select*fromStudentwhereEmai1i snul1)beginupdateStudentsetEmai1=,未知whereEmai1isnul1print,错误地址’endelse意事项
7.selectStudentNo,StudentName,EmailfromStudent子查询中的列假如没有在父查询中出现是不会被显示的子查询无法在Text、NText和Image类型上运用子查询中可以运用where、group by及having子句对比
8.>In子查询返回的是一个查询结果集;exists子查询返回的是布尔值应用(什么场合运用连接查询;什么时候运用子查询)
9.>须要返回多表字段时运用连接查询>只返回一个表中数据时运用子查询
四、事务、视图和索引事务
1.)概述1事务是一个逻辑工作单元,事务中的多个操作视为一个整体,操作之间要么同时成功,要么同时失败事务可以保证数据的有效性和一样行)特点2>原子性事务中的全部操作是整体,要么同时成功,要么同时失败>一样性事务全部操作具有一样状态>隔离性事务和事务之间是隔离的,不行相互操作的>永久性事务成功执行后数据将被永久存储到数据库系统中)分类3>自动提交事务Server中将insert、delete及Update操作语句就是依据自动方式进行提交的>隐式事务通过set IMPLICIT_TRANSACTIONS on启动事务,开启后其后的语句自动以事务的方式进行执行当遇到提交语句后后面默认开启了一个新的事务(隐式事务实际中基本不被运用)>显式事务显式事务是通过特定的语句进行开启显式事务是实际应用中应用较多的方式)语句4>Begin tran[saction]开启事务>Commit transaction提交事务Rollback transaction回滚事务(取消前面的操作))示例5一业务银行转账业务(Rose从Mike借了块钱)一转出一方(借方)账户余额削减一转入一方(贷方)账户余额增加一定义变量存储错误编号declare@errint=O一a、开启事务begintran一b、执行转账操作一Mike账户转出updateaccountsetblance=blance-490whereUserName=,MikeJset@err=@err+@@ERRORupdateaccountsetblance=blance+490whereUserName=,Roseset@err=@err+@@ERROR一推断是否产生过错误if(@err=0)--提交事务commitelse一回滚事务rollbackgo)应用6一些大型项目中基本都会应用,如银行、电商、电信等业务中事务确定涉及到多个表)缺点7事务会降低语句的执行效率视图
2.1视图是一个虚拟表,视图中存储的是查询语句.运用视图时其实实在运用他的查询语句.)创建方式2>视图方式>语句方式)语法3Create view视图名称As查询语句)示例4--a、定义视图createviewvw_resultasselecta.StudentNo,StudentName,Sex,GradeName,d.SubjectName,StudentResult,ExamDatefromstudentaleftjoinGradebona.Gradeld=b.GradeldleftjoinResultcona.StudentNo=c.StudentNoleftjoinSubjectdone.Subjectld=d.Subjectld go)特点5>简洁视图(涉及一个表或不存在引用关系的)可以像表一样进行CRUD操作>假如视图中查询语句涉及多个表且存在引用关系时一般无法执行删除操作删除数据库
3.)语法1Drop database数据库名)示例2—
2、删除数据库一练习删除名为Xoa的数据库一检查数据库是否存在--databases视图,存在及Master数据库中ifexists(select*fromsys.databaseswherename=,xoa,)dropdatabaseXoa)建议3删除数据库之前尽量运用推断语句检查数据库是否存在建表
4.)数据类型1>字符类型令Char非unicode的固定长度的字符类型Varchar非unicode的可变长度的字符类型意事项)6令Ncharunicode的固定长度的字符类型>视图可以嵌套运用,嵌套层次一般不要超过3层(效率下降)>视图的查询语句不能运用虚拟表或表变量>视图中不能运用order by子句,除非运用了top视图的优点)7>视图中不能运用into子句>将困难的查询语句简洁化>将多表查询变为简化为单表查询索引
3.概述)1>有效爱惜隐私数据索引就是对数据进行重排的一种方式,通过全部可以有效的提高数据的检索(查询)速度.索引中存储了数据或数据的位置)语法2Create[unique][clustered|nonclustered]index索弓|名On表名(字段名)Fillfactor=数值(0~100))说明3>索引分类今唯一索引创建唯一约束将自动创建唯一索引,唯一索引要求数据唯一令主键索引主键索引是唯一索引的一种特例令聚集索引对表数据的物理依次进行重排,一个数据表只能创建一个聚集索引非聚集索引对表中数据建立索引页一个表可以创建多个非聚集索引>FiHfactor(填充因子)设置索引页填充的百分比取值为0^
100.填充因子建议设置在5080左右〜)示例4一为学生信息表的姓名创建索引createnonclusteredindexidx_studentNameonstudentstudentname)优点5>索引可以保证数据的唯一行>索引可以提高检索效率)缺点6>索引会增加额外的存储空间>索引会降低CUD的效率)建议7)查询的列8>创建原则令建议在较为短小的列上创建索引令经常被上创建索引今经常进行表连接的列上创建索引>不建议创建原则列相同值较多的不建议创建索引令数据较少的列不建议创建索引>维护原则弋定期进行重建索引)查看索引9>视图方式—indexes视图,存储了索引的相关信息select*fromsys.indexes>存储过程一存储过程查看相关表的索引信息sp_heIpindexstudent)删除索引10>语法Drop index表名.索引名>示例—
5、删除索引一删除表时索引同时被删除ifexistsselect*fromsys.indexeswherename=,idx_studentNamedropindexstudent.idx_studentName
五、存储过程存储过程就是一个出名称的Sql代码在存储过程中封装了确定的业务规则存储过程可以有参数和返回值分类
2.)系统存储过程1>概述系统由数据库系统进行管理和维护,通常完成特定的操作系统存储过程是运用sql语句进行编写的>命名Sp_存储过程名称>示例--sp_databases列出当前数据系统内的全部数据库信息sp_databases-sp_tables列出当前数据库下全部的表/视图信息sp_tabl.essptablesgrade—sp_help列出数据库内对象的信息sp_help一sp_helpindex查看指定表的索引信息sp_helpindexstudent一查看相关表的约束信息sphelpconstraintstudent一更改数据库名称spjenamedb myschool,schooT)扩展存储过程2>概述扩展存储过程是运用其他编程语句进行编写的,扩展存储过程一般一D11的形式存在>命名Xp_存储过程名称>示例Xp_cmdshell)自定义存储过程3>概述自定义存储过程是运用Sql语句进行编写的存储过程,由用户进行创建和维护.语法3Create proc[edure]存储过程名称@参数名类型匚默认值][output][,……]AsSql语句段说明
4.>参数存储过程的参数可以包含输入、输出参数同时可以对参数设置默认值>语句段存储过程内的语句段可以包含变量定义、事务、推断及循环语句示例
5.一a、无参存储过程一无参数只执行一个操作的存储过程createprocproc_selectasselect*fromstudentgo一c、带参数存储过程一依据学生名称查询学生成果信息createprocproc_result--输入参数@namevarchar20asselecta.*fromresultaleftjoinstudentbona.studentno=b.studentnowhereb.studentname=@namego一e、多参数存储过程一查询指定科目的全部不及格学生信息一带有默认值的参数列表中,默认值参数须要定义在参数列表的最终createprocproc_select4©namevarchar50,@scoreint=60asselect*fromStudentwhereStudentNoinselectStudentNofromResultwhereStudentResult@scoreandSubjectld=seiectSubjectIdfromSubjectwhereSubjectName=@namego一调用存储过程一传递一个参数默认值参数被省略了proc_select4,走进java编程世界’proc_select4,走进java编程世界’,30一g、带有输出参数的存储过程一修改存储过程proc_del3,要求存储过程可以返回受影响行数一假如输出参数带有默认值可以将输出参数定义参数列表的最终一否则,建议将输出参数定义在参数列表德前面createprocproc_del4一参数@rowsint=0output,©namevarchar50as一变量declare@errint=0一开启事务begintran一查询并将年级信息保存到历史表中insertintoGrade_historyselect*fromGradewhereGradeName=@nameset@err=@err+@@ERROR一删除指定名称的年级信息deletefromGradewhereGradeName=@nameset@err=@err+@@ERROR一推断是否产生错误if@err=0beginset@rows=2一提交事务commitendelse一回滚事务rollbackgo执行
6.存储过程名[参数列表]或Execute]存储过程名[参数列表]注省略exec每次只能执行一个存储过程;带有exec一次可以执行多个存储过程令Nvarcharunicode的可变长度的字符类型令Text>数值Int>float、decimal、number>布尔类型BitA日期类型Datetime>二进制Image)语法2Create table表名(字段名类型[(长度)][特征])注特征一非空(Not null)>自增(Identity))示例3一练习创建部门表(Department)--记录Id(deptld,int,自增)、部门名称(deptName,varchar
(50),非空)优点
7.>代码集成化>执行效率高(一般语句执行过程中须要执行语法检查、编译优化、执行而存储过程在创建过程中已经完成了语法检查和编意事项
8.译优化调用过程中将被干脆执行)>定义参数过程中默认值参数定义在参数列表的最终>调用带有输出参数的存储过程时须要在输出参数的后面运用output关键字进行标识删除存储过程
9.>调用带有输出参数的存储过程时须要运用execDrop proc存储过程名称lO.Raiserror>作用生成错误消息并在消息窗口中对错误消息进行显示>格式Raiserror[消息Id消息内容变量],消息级别,状态)[with log.>说明令消息级别的取值为25,其中0~18一般用户可用,1925需具〜〜有sysadmin管理员身份才能运用令With log标识将错误数据插入到日志中>示例一一
2、raiserrorraiserrorok,2,1raiserrorok,12,1
六、数据库设计优势
1.>削减数据冗余,节约存储空间>便利应用程序的开发>提高语句执行效率>保证数据为精确性和完整性生命周期
2.立项一一招投标一一启动会一--需求调研需求调研说明书--一系统设计实现测试培训试运行上线后期维护createtableDepartmentdeptIdintidentity1,1,deptNamevarchar50notnull数据完整性4>实体完整性令要求唯一标识每行记录令实现主键、唯