SQL Server基础培训PPT课件

.,1,基础培训,郑顺华系统集成事业部2007年4月,.,2,培训内容,培训内容第一章：数据库基础知识介绍第二章：SQLServer的安装与系统结构第三章：SQLServer的日常使用与管理第四章：SQLServer的开发应用第五章：数据库性能问题培训安排,.,3,培训目标熟悉数据库基础知识掌握SQLServer的安装与数据库的创建掌握简单的日常管理进行SQL开发总结：独立创建一个开发系统的过程初步开发的能力,.,4,第一章数据库基础知识,.,5,内容,内容数据库的组成与简单概念当前流行的数据库MSSQLServer2000简介目标和要求掌握数据库基本概念,.,6,什么是数据库系统?,数据库系统：指引进数据库技术后的计算机系统。包括：硬件系统，数据库集合，数据库管理系统(DBMS)及相关软件，数据库管理员和用户。,.,7,数据库系统基本组成,物理设备,DBMS,Applications(应用),网络,协议介质,接口,DBTools(管理工具),存储管理数据库管理数据库中的对象管理1.表单2.视图3.触发器4.索引5.存储过程4.函数、包,.,8,数据库内的对象-表,部门序号,LastName,FirstName,Extension,CtryCode,LastMod,行,列,存储数据的地方由行（row记录）和列（column字段）组成,类型,.,9,数据库内的对象,部门序号,LastName,FirstName,Extension,CtryCode,LastMod,触发器,存储过程,.,10,主键约束,一个表只能包含一个PRIMARYKEY约束。如果没有在PRIMARYKEY约束中指定CLUSTERED或NONCLUSTERED，并且没有为UNIQUE约束指定聚集索引，则将对该PRIMARYKEY约束使用CLUSTERED。在PRIMARYKEY约束中定义的所有列都必须定义为NOTNULL。如果没有指定为空性，加入PRIMARYKEY约束的所有列的为空性都将设置为NOTNULL。,.,11,唯一性约束,如果UNIQUE约束中没有指定CLUSTERED或NONCLUSTERED，则默认为NONCLUSTERED。每个UNIQUE约束都生成一个索引。例：创建表职工（职工号，姓名，身份证号）的同时定义身份证号取值唯一。T-SQL语句如下：CREATETABLE职工(职工号char(8)NOTNULL，姓名char(10)，身份证号char(18)NULLCONSTRAINTuni_sfzUNIQUE）例：为存在的表职工（职工号，姓名，身份证号）定义身份证号取值唯一的约束。T-SQL语句如下：ALTERTABLE职工ADDCONSTRAINTuni_sfzUNIQUENONCLUSTERED(身份证号)例：删除上例建立的主键约束。T-SQL语句如下：ALTERTABLE职工DROPCONSTRAINTuni_sfz,.,12,DEFAULT约束,若将表中某列定义了DEFAULT约束后，用户在插入新的数据行时，如未指定该列值，系统自动将该列值赋为默认值（默认值可以是空值）。使用企业管理器定义、删除DEFAULT约束语法格式：CONSTRAINT约束名DEFAULT默认约束值FOR列名使用T-SQL语句删除DEFAULT约束语法格式：DROPCONSTRAINT约束名,.,13,DEFAULT约束示例,例：创建表学生（学号，姓名，性别）的同时定义性别的默认值为男。T-SQL语句如下：CREATETABLE学生(学号char(8)NOTNULL，姓名char(10)，性别char(2)DEFAULT男）例：为存在的表学生（学号，姓名，性别）定义性别的默认值为男。T-SQL语句如下：ALTERTABLE学生ADDCONSTRAINTdft_xsDEFAULT男FOR性别例：删除上例建立的默认约束。T-SQL语句如下：ALTERTABLE学生DROPCONSTRAINTdft_xs,.,14,外键结束,外键(FK)是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。在增加外键结束：ALTERTABLEMS_CF02ADDCONSTRAINTFK_MS_CF01_CFSB_MS_CF02_CFSBFOREIGNKEY(CFSB)REFERENCESMS_CF01,.,15,规则,规则也是一种数据库对象，可以绑定到一列或多列上，作用与CHECK约束相似，在插入数据行时，指定接受的数据值的范围。规则对象在功能上与CHECK约束是一样的，在使用上有所区别：CHECK约束是和表的定义联系在一起的，删除表的同时CHECK约束也被删除规则对象作为一种单独存储的数据库对象，独立于表之外（用CREATERULE定义），删除表时并不删除规则对象（用DROPRULE删除），,.,16,创建规则对象,使用T-SQL语句CREATERULE创建规则对象语法格式：CREATERULE规则名AS规则条件例：定义规则对象：设置成绩在0分和100分之间。CREATERULEcj_rlAScj=0andcj、=、(不等于)字符串连接运算符+逻辑运算符and（与）、or（或）、not（非）位运算符按位与(&)、或（|）、异或()、求反（）（位运算符在中已经很少用到）,.,208,运算符,运算符的优先级由高到低为：（）、&|NOTANDOR,.,209,2、流程控制语句,.,210,流程控制语句,流程控制语句用于控制SQL语句、语句块或存储过程的执行流程。IFELSE语句格式：IF布尔表达式SQL语句或语句块ELSESQL语句或语句块例：在学生情况表（XSQK）中，查找学号为02020112的学生记录，如果有，则显示此记录，如果无，则显示“此学生不存在！”。Ifexists(select*fromxsqkwhere学号=02020112)select*fromxsqkwhere学号=02020112Elseprint此学生不存在！,语句块是指由BEGINEND所封装的多条语句,.,211,流程控制语句,练习：在课程表（XSKC）中，查找课程号为A006的记录，如果有，则显示“此课程已存在”，如果无，则插入此课程信息（A006，JAVA程序设计，4，4，3）。Ifexists(select*fromxskcwhere课程号=A006)print此课程已存在！Elseinsertintoxskcvalues(A006，JAVA程序设计，4，4，3)思考：现要在学生成绩表中（XSCJ），插入某个学生的成绩信息（02010407，A006，80，4），请根据数据完整性要求，设计SQL语句。,ifexists(select*fromxscjwhere课程号=A001and学号=02010407)print此学生成绩信息已存在！elseifexists(select*fromxsqkwhere学号=02010407)ifexists(select*fromxskcwhere课程号=A001)insertintoxscjvalues(02010407,A001,80,2)elseprintA001号课程不存在，请先添加A001号课程信息！elseprint02010407号学生不存在，请先添加02010407号学生信息！,.,212,流程控制语句,2.无条件转移语句GOTO语句格式：GOTO标号无条件转移到标号处。3.WHILE、BREAK和CONTINUE语句格式：WHILE布尔表达式BEGIN命令行或程序块BREAKCONTINUEEND,BREAK：使程序从循环中跳出；CONTINUE：使程序跳过循环体内其后续语句，结束本次循环，执行下一次循环。,.,213,流程控制语句,例2:DECLAREntinyintSETn=5IF(nBETWEEN4and6)BEGINWHILE(n0)BEGINSELECTnASNumber,CASEWHEN(n%2)=1THENEVENELSEODDENDASTypeSETn=n-1ENDENDELSEPRINTNOANALYSISGO,例1将1到100的整数累加，求和。declareIintdeclaresumintsetI=1,sum=0whileI,=,=30,productid,total_quantity,2,35,3,45,.,247,找出最的几条记录,TOPnPERCENT列表前面的几条记录如果未指定PERCENT，n就是返回的行数。如果指定了PERCENT，n就是返回的结果集行的百分比,USEnorthwindSELECTTOP5orderid,productid,quantityFROMorderdetailsORDERBYquantityDESC,USEnorthwindSELECTTOP5percentorderid,productid,quantityFROMorderdetailsORDERBYquantityDESC,.,248,数据修改,Insert语句：插入新记录Delete语句：删除已经存在的记录Update语句：修改已经存在的记录,.,249,插入新记录,USEnorthwindINSERTcustomers(customerid,companyname,contactname,contacttitle,address,city,region,postalcode,country,phone,fax)VALUES(PECOF,PecosCoffeeCompany,MichaelDunn,Owner,1900OakStreet,Vancouver,BC,V3F2K1,Canada,(604)555-3392,(604)555-7293),INSERTINTOtableVALUES(.)INSERTINTOTABLESELECT(),PartialSyntax,例子,.,250,删除已经存在的记录,使用DELETE语句来删除可以删除一条或多条记录使用Where子句限定删除的范围记入日志使用TRUNCATETABLE语句进行整表删除删除所有记录不记日志，速度快,USEnorthwindDELETEcustomersWHEREcustomerid=11023,DELETEtable_nameWITH(.n)|view_nameWHERE,PartialSyntax,例子,.,251,如何修改数据？,使用WHERE子句限定要修改哪些记录使用SET关键字来赋值,USEnorthwindUPDATEproductsSETunitprice=(unitprice*1.1),UPDATEtable_nameSETcolumn_name=expression|DEFAULT|NULLFROM,.nWHERE,PartialSyntax,例子,.,252,多表连接查询,多表连接查询什么是连接(join)?内连接（InnerJoins）外连接（OuterJoins）交叉连接（CrossJoins）f合并多个查询结果集union,.,253,什么是连接（Join）,通过联接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据INNEROUTER内联接OUTERJOIN外联接CROSSJOINJOIN交叉联接,.,254,内连接(InnerJoins),USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_id,默认为=,.,255,外连接（OuterJoins）,USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersLEFTOUTERJOINsalesONbuyers.buyer_id=sales.buyer_id,.,256,交叉连接（CrossJoins）,USEjoindbSELECTbuyer_name,qtyFROMbuyersCROSSJOINsales,默认为没有=,.,257,多于两个表的连接,SELECTbuyer_name,prod_name,qtyFROMbuyersJOINsalesONbuyers.buyer_id=sales.buyer_idJOINproduceONd_id=d_id,SELECTbuyer_name,prod_name,qtyFROMbuyers,sales,produceWherebuyers.buyer_id=sales.buyer_d_id=d_id,.,258,自身连接(Selfjoining),USEjoindbSELECTa.buyer_idASbuyer1,d_id,b.buyer_idASbuyer2FROMsalesaJOINsalesbONd_id=d_idWHEREa.buyer_idb.buyer_id,.,259,合并多个结果集,UNION字句必须满足以下条件:兼容的数据类型字段数相同列出的字段排序相同,USEnorthwindSELECTname=(firstname+lastname),city,postalcodeFROMemployeesUNIONSELECTcompanyname,city,postalcodeFROMcustomers,.,260,第五章数据库性能问题,.,261,内容,产生性能问题的原因提高数据库性能的一些措施SQL性能优化案例分析,.,262,产生性能的原因,网络问题：网络速度慢硬件问题Cpu内存：内存不足硬盘：I/O吞吐量小，形成了瓶颈效应操作系统&数据库系统应用程序（程序设计、数据库设计、SQL语句）,.,263,可调整的部分:网络硬件操作系统数据库应用程序许多人在试图提高数据库应用程序性能时只将注意力放在了前三项：网络、硬件、操作系统最少60%的数据库性能问题是由应用程序引起的,可调整的地方,应用程序,数据库,操作系统,硬件,网络,WrongFocus,.,264,提高数据库性能的措施,磁盘作RAID，以获取更多的I/O速度，RAID分成硬件RAID与Windows2000软件RAID，建议使用硬盘RAID，因为它速度更快。把TempDB设为自增长，把TempDB放在快速的磁盘上，初期加大TempDB数据库的文件大小，因为频繁的扩大文件有损I/0性能。把数据库文件分成多个并保存到不同的磁盘上，例如，可以分别在三个硬盘驱动器上创建三个文件（Data1.Mdf、Data2.mdf和Data3.mdf）,这样如果计算机具有多个处理器，SQLServer可以对数据执行并行扫描。如果表的文件组包含多个文件，可对单个表执行多个并行扫描。每当对表进行有序访问时，都创建单独的线程以并行读取每个文件将事务日志创建在物理上单独的磁盘或RAID（独立磁盘冗余阵列）设备上。将事务日志文件按序列写入，因此使用单独的专用磁盘可使磁头保持在下一个写入操作的位置。,.,265,SQL性能优化,使用索引根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。所有对业务数据表的查询,必须用到有效的索引正确的SQL语句尽量避免阻塞或者死锁程序中的事务尽量做到最小化查询时不要返回了不必要的行和列OLAP与OLTP分开设计,.,266,SQL优化原则,Like语句likea%使用索引like%a不使用索引用like%a%查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。限制返回行在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。使用TOP语句来限制返回结果当返回多行数据时,尽可能不使用光标，它占用大量的资源,应该使用datastore,.,267,SQL优化原则,注意UNion和UNionall(返回所有重复行)的区别。UNIONall较好一般不要用如下的字句:“”,“!=”,“!”,“!”,“!=”,“!”,等还是不能优化，用不到索引不要在WHere字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代.还可以变通写法：WHERESUBSTRING(firstname,1,1)=m改为WHEREfirstnamelikem%（索引扫描）,但MIN()和MAX()能使用到合适的索引,.,268,SQL优化原则,Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。select*fromYF_KCMXwhereYPXHin(12,13)Select*fromYF_KCMXwherebetween12and13select的Where字句选择所有合适的行，GroupBy用来分组个统计行，Having字句用来剔除多余的分组。这样GroupBy个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果GroupBY的目的不包括计算，只是分组，那么用Distinct更快,.,269,SQL优化原则,注意细节select*formZY_FYMXwhereFYDJ3000分析在此语句中若FYDJ是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。应改为select*formZY_FYMXwhereFYDJ3000.00,.,270,SQL优化原则,SQL条件不是越多越好,selectjlxh,fyxh,zfbl,yzxhfromzy_fymxwherezyh=44645andjlxhin(selectmin(jlxh)fromzy_fymxwherezyh=44645andexists(selectjlxhfromzy_bqyzwherezyh=44645andlsbz1andsppb=0andlsyz=0andyplx0andyzxh=zy_bqyz.jlxhandkssj=2008-3-170:0:0.000andjfrq=2008-3-1823:59:59.000)AND(A.BRID=MS_CF01.BRID)FROMMS_CF01,YS_MZ_JZLS,MS_BRDAWHERE(MS_CF01.JZXH=YS_MZ_JZLS.JZXH)AND(MS_CF01.YFSBin(1)AND(MS_CF01.ZFPB=0)AND(MS_CF01.SHBZ=0)AND(YS_MZ_JZLS.JZZT1)AND(MS_CF01.KFRQ=2008-3-1823:59:59.000)ANDMS_CF01.BRID=MS_BRDA.BRIDORDERBYCFSB1ASC,.,272,案例分析,案例分析一其数据库容量为28GB，根据对MS_CF01和MS_CF02的大小统计，其中MS_CF02记录条数为1000万条；发药统计时间一个月，耗时分钟仍然无法得到结果，严重影响正常业务，遂中止。原先统计的SQL语句如下：selectsum(MS_CF02.YPSL*MS_CF02.YPDJ*MS_CF02.CFTS)astotalfromMS_CF01,MS_CF02whereMS_CF01.CFSB=MS_CF02.CFSBandMS_CF01.CFLX=1and(MS_CF01.FYBZ=1orMS_CF01.FYBZ=3)andMS_CF01.FYRQ=”2004.3.100:00:00”andMS_CF01.FYRQ=”2004.3.100:00:00”/得到该时间段内最小的CFSB,例如3198724selecttop1CFSBfromMS_CF01whereFYRQ=3198724andMS_CF02.CFSB0andGCSLDCSLandYKZF=0ls_select+=andYK_TYPK.YPXHnotin(selectYPXHfromYK_KCMX)ls_select+=groupbyYK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL,.,275,案例分析,显然是NOTIN语句导致速度很慢,然后把该语句改成:ls_select+=selectYK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL,0ls_select+=fromYK_TYPKls_select+=whereXTSB=+string(base_info.syscode)+andDCSL0andGCSLDCSLandYKZF=0ls_select+=andnotexists(selectYPXHFROMYK_KCMXWHEREYK_TYPK.YPXH=YK_KCMX.YPXH)ls_select+=groupbyYK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL“结果5秒内执行完成,.,276,案例分析,案例分析三以下SQL的功能是某家医院用于作日报时显示哪些收费员还没有做过当天的个