SQL数据库学习笔记与心得.docx

SQL数据库学习笔记与心得SQL数据库学习笔记与心得SQL数据库学习笔记与心得0961140107SQL是市场占有率最大的数据库之一，是信息化社会的产物，是整理、查询、分析数据的强有力工具。我对于SQL数据库的学习是在实验与探索之中度过的。下面将我本学期所学的主要知识简单总结如下：共分四大块：一、数据库基本原理；二、SQL标准语言；三、数据库设计；四、数据库安全。一、数据库基本原理数据库技术是计算机领域中发展最快的技术之一。数据模型是数据库系统的核心和基础。常用的数据模型有层次模型、网状模型、关系模型、面向对象模型、对象关系模型。其中关系模型是目前最重要的一种数据模型。关系数据库是我们需要掌握的重点。关系模型中常用的关系操作包括：查询操作和插入、删除、修改操作两大部分。关系的查询表达能力很强，是关系操作中最主要的部分。二、SQL标准语言SQL（StructuredQueryLanguage），即结构化查询语言，是关系数据库的标准语言。SQL标准语言从1986年公布以来随着数据库技术的发展不断发展，不断丰富。SQL功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKESQL之所以能够为用户和业界所接受，并成为国际标准，是因为它是一个综合的、功能极强同时又简洁易学的语言。SQL集数据查询、数据操纵、数据定义林巧和数据控制功能于一体。有如下特点：1）综合统一；2）高度过程化；3)面向集合的操作方式；4）以同一种语法结构提供多种使用方式；5）语言简洁，易学易用。SQL的数据定义语句：创建删除修改表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX数据库查询是数据库的核心操作。也是数据库学习的重点和难点。SQL提供了SELECT语句进行数据库的查询,该语句具有灵活的使用方式和丰富的功能。其一般格式为：SELECTALL|DISTINCTFROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；SELECT语句既可以完成简单的单表查询，也可以完成复杂的连接查询和嵌套查询。(一)、单表查询1、选择表中的若干列：2、选择表中的若干元组;2）查询满足条件的元组。WHERE子句常用的查询条件有3、ORDERBY子句。4、聚集函数。5、ORDERBY子句(二)、连接查询1、等值与非等值连接查询；2、自然连接；3、外连接；4、复合条件连接。(三)、嵌套查询1、带有IN谓语的子查询；2、带有比较运算符的子查询；3、带有ANY(SOME)或ALL谓语的子查询；4、带有EXISTS谓词的子查询。(四)、集合查询。数据的更新操作有三种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。三、数据库设计数据库设计是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，使之能够有效地存储数据，满足各种用户的应用需求（信息要求和处理要求数据库设计的基本步骤：1、数据库设计的准备工作；2、数据库设计的过程(六个阶段)：1）需求分析阶段；2）概念结构设计阶段；3）逻辑结构设计阶段；4）数据库物理设计阶段；5）数据库实施阶段；6）数据库运行和维护阶段四、数据库安全数据库的安全性是指保护数据库，防止因用户非法使用数据库造成数据泄露、更改或破坏。课本中讲述了三类计算机系统安全性问题：技术安全类、管理安全类和政策法律类。如何控制数据库的安全性?其常用的方法有:1)用户标识和鉴定2）存取控制;3)视图;4)审计;5)密码存储数据库的完整性：即数据的正确性和相容性防止不合语义的数据进入数据库。例如:学生的年龄必须是整数，取值范围为14-29；学生的性别只能是男或女；学生的学号一定是唯一的；学生所在的系必须是学校开设的系；以上是我对SQL数据库重点知识的理解。通过学习本课程和上机实验，我了解了数据库的概念及其重要性，还有其今后的发展方向，这将有利于我今后更好地把握时代之脉搏。数据库一、数据库基础知识1、发展阶段萌芽期（文件系统）初级阶段（层次模型和网状模型）主要代表为IBM公司的IMS（信息管理系统）中级阶段（关系型数据库）主要代表为甲骨文公司Oracle，微软SQLServer和IBM的DB2高级阶段（关系对象型数据库）2、数据模型层次模型：结构型数据库主要数据模型，代表数据库为IMS网状模型：构成网状数据库的主要数据模型关系模型：采用二维表结构采用关系模型组织数据查询效率有时不高面向对象模型：3、数据库系统结构（1）用户级数据库：外模式（2）概念机数据库：模式（3）乌力吉数据库：内模式，最接近数据的物理存储与组织级别4、数据库三模式（1）外模式：模式的子集，应用程序都是和外模式打交道（2）模式：（3）内模式：只有一个内模式5、数据库的二级映像（1）外模式/模式映像：保障物理独立性（2）模式/内模式映像：保证逻辑独立性6、关系模型完整性约束实体完整性（所有主属性都不能取空值）；参照完整性（多个表之间）；用户自定义的完整性7、SQL：（结构化查询语言）T-SQL：SQLServer，包括数据定义语言，数据操作语言，数据查询语言，数据控制语言PL_SQL：Oracle，声明，执行体开始，异常处理，执行体结束。二、SQLServer数据库基础1、系统数据库：包括Resource数据库（只读数据库）、master数据库、msdb数据库、tempdb数据库、model数据库（数据库模板）；系统数据库存储在隐藏的系统表中2、用户数据库：数据库的存储机构包括逻辑存储结构和物理存储结构SQLServer数据文件类型：主数据文件（mdf文件，只能有一个）、辅助数据文件（ndf，可以没有，也可以有多个）文件、日志文件（ldf，记录对数据库记录的操作，至少有一个，可以有多个）；数据存储的基本单位是“页”，一个页的大小为8Kb，一个区有8个页。（1）创建数据库名称遵循规则：第一个字符必须是字母或“_”“”“#”；数据库名称不能是Transaction-SQL的保留字；不允许嵌入空格或其他特殊字符。USEmasterifexists(select*fromsysdatabaseswherename=mytest)dropdatabasemytestcreatedatabasemytestonprimary(name=mytest,filename=D:ProgramFilesSQLServerDataSQLServerDataDatamytest.mdf,size=5MB,maxsize=100MB,filegrowth=15%),(name=mytest1,filename=D:ProgramFilesSQLServerDataSQLServerDataDatamytest1.mdf,size=5MB,maxsize=100MB,filegrowth=15%)logon(name=mytest_log,filename=D:ProgramFilesSQLServerDataSQLServerDataDatamytest_log.ldf,size=2MB,filegrowth=1MB)Go（2）删除数据库DROPDATABASE数据库名（3）收缩数据库手动收缩和自动收缩3、数据表数据完整性：实体完整性约束，表中不能存在相同的数据项；域完整性，给定列的输入内容具有有效性；引用完整性，保存表之间的定义关系；自定义完整性主键：一个表只能有一个主键，可以没有主键，选择时，要确保最少性和稳定性外键：确保子表中的数据对应主表中的主键或者唯一键常用数据类型：数字类型：int，float，decimal（必须制定范围和精度）文本类型：char，varchar，nvarchar，textBit数据类型：bool日期时间类型：datatime货币型数据：money（1）创建数据表（2）删除数据表USETrainingBasegoifexists(select*fromsysobjectswherename=Trainee)droptableTraineecreatetableTrainee()TraineeNointNOTNULL,TraineeNamenvarchar(50)notnull,Sexbitnotnull,GradeIDintnotnull,Phonenvarchar(50)null,Addressnvarchar(255)null,BornDatedatetimenotnull,Emailnvarchar(50)null,IdentityCardvarchar(18)notnull（3）创建删除约束主键约束：主键值必须唯一ALTERTABLETraineeADDCONSTRANINTPK_TraineePRIMARYKEY(TraineeNo)非空约束：ALTERTABLETraineeADDCONSTRANINTPK_TraineePRIMARYKEY(TraineeNo)唯一约束：ALTERTABLETraineeADDCONSTRANINTUQ_IdentityCardUNIQUE(IdentityCard)检查约束：ALTERTABLETraineeADDCONSTRANINTCK_BornDateCHECK(BornDate1980-1-1)默认约束：ALTERTABLETraineeADDCONSTRANINTDF_AddressDEFAULT(地址不详)forAddress外键约束：ALTERTABLETraineeADDCONSTRANINTFK_GradeFOREIGNKEY(GradeId)REFERENCEGradeId4、SQLServer身份验证（1）Windows身份验证（更安全）（2）SQLServer身份验证（Windows身份验证改为SQLServer身份验证的方式：首先，属性更改，然后改安全性中的内容）5、访问权限对数据库服务器控制权限对数据库中数据控制权限添加用户6、导入导出数据7、数据库优化（1）调整数据库结构的设计（2）调整应用程序机构设计（3）调整数据库SQL语句（4）调整服务器内存分配避免多表连接查询使用同一的SQL语句规范批量导入数据时，设置恢复模式为“大容量日志恢复模式”，导入前禁用索引。一、T_SQL1、运算符算术运算符：+、-、*、/，%赋值运算符：=比较运算符：，=，!=，=逻辑运算符：AND，OR，NOT2、表操作插入：一般插入一行，INSERTINTOGradeGradeNameVALUES(实习阶段)，可用关键字DEFAULT表示用默认值修改：可更新一行数据，也可更新多行数据，也可能一行数据都不更新，UPDATE表明SET（字段名）=更新值WHERE更新条件UPDATEResultSETTraineeResult=TraineeResult+5WHERETraineeResult=60（4）创建新表：SELECTINTO,新表必须不存在SELECTTrainee.TraineeName,Trainee.Address,Trainee.EmailINTONewAddressListFROMTraineeWHERE11（使查询条件不成立）查询示例：SELECT*FROMTraineeASAWHEREA.TraineeNoIN(SELECTB.TraineeNoFROMResultBWHEREB.TraineeResult80ANDB.Subject=(SELECTC.SubjectNoFROMSubjectCWHEREUPPER(C.SubjectName)=SQLSERVER)SELECT*FROMTraineeAJOINResultBONA.TraineeNo=B.TraineeNoJOINSubjectCONC.SubjectNo=B.SubjectNoWHEREUPPER(C.SubjectName)=SQLServerANDB.TraineeResult804、多表查询（1）内联接：从两个或两个以上的表组合中挑选出符合联接条件的数据，如果数据无法满足联接条件则将其丢弃，在内联接中，参与联接的表的地位是平等的。SELECT表名1.字段名，表名2.字段名，FROM表名1，表名2WHERE表名1.字段名=表名2.字段名SELECTTrainee.TraineeName,Result.SubjectNo,Result.ExamDate,Result.TraineeResultFROMTrainee,ResultWHERETrainee.TraineeNo=Result.TraineeNoSELECT表名1.字段名，表名2.字段名，FROM表名1，表名2INNERJOIN表名2ON表名1.字段名=表名2.字段名SELECTTrainee.TraineeName,Result.SubjectNo,Result.ExamDate,Result.TraineeResultFROMTraineeINNERJIONResultON(Trainee.TraineeNo=Result.TraineeNo)（2）外联结：外联结中参与联接的表有主从之分，以主表的每行数据匹配从表的数据列，将符合联接条件的数据直接返回到结果集中；对那些不符合联接条件的列，将被填上NULL值后再返回到结果集中。左外联接LEFTJOIN=LEFTOUTERJOINSELECTT.TraineeName,R.SubjectNo,R.TraineeResultFROMTrainee（主表）ASTLEFTJOINResultASRONT.TraineeNo=R.TraineeNo右外联接RIGHTJOIN=RIGHTOUTERJOINSELECTT.TraineeName,R.SubjectNo,R.TraineeResultFROMTraineeASTRIGHTJOINResult（主表）ASRONT.TraineeNo=R.TraineeNo（3）交叉连接5、使用Union合并多个查询结果所有查询的列数和列的顺序必须相同；要合并的数据类型必须相同，至少也要可以转换合并的查询结果集的字段名称以第一个查询结果的字段名称为名，其他的查询集的字段名称会被忽略SELECT查询语句1UNIONALLSELECT查询语句2三、数据查询安全性和性能优化1、SQL注入：将SQL代码插入到应用程序的输入参数中，之后，SQL代码将被传递到数据库执行，从而达到对应用程序的攻击目的。原理：检测注入点判断数据库类型传递恶意代码上传木马，盗取账号，获取管理员权限，发现Web目录防范：限制错误信息的输出；使用参数命令传递参数；使用存储过程；限制输入长度；URL重写技术；传递参数尽量不用字符串2、SQL优化：低效SQL危害：系统响应变慢（8秒定律）；死锁；客户失去信心，软件失败根源：硬件原因；没有建索引，或者SQL没有走索引；SQL过于复杂；频繁访问数据库SQL执行原理：解释解析优化编译执行优化SQL语句：完善开发管理；检测SQL查询的效率查询SQL语句查询时间SETSTATISTICSIOONSETSTATASTICSTIMEON清楚缓存DBCCDROPCLEANBUFFERSDBCCFREEPROCCACHESQL优化：避免对索引字段进行数值操作；使用Top语句限制返回的数据集；SELECT字段需要多少提取多少；ORDERBY后跟的尽量是索引字段；使用存储过程优化。四、T-SQL程序1、变量全局变量：先声明，后赋值，必须以作为标记前缀：DECLAREvariable_nameDateType;使用SET或者SELECT为变量赋值，SET一般用于赋给变量指定的数据常量，不支持多个变量赋值和表达式返回多个值，表达式未返回值时，变量被赋NULL值；SELECT从表中查询数据，然后赋给变量，支持多个变量赋值，在返回多个值时，将返回最后一个值赋给变量，在表达式未返回值时，变量保持原值。SET语句执行效率比SELECT语句要高局部变量：必须与标志作为前缀2、数据类型转换使用CAST转换数据类型：CAST（expression(有效表达式)ASdata_type(length)）SELECTGradeIdAS阶段,CAST(COUNT(GradeId)ASCHAR(2)+人AS人数FROMTraineeGROUPBYGradeId使用CONVERT转换数据类型：CONVERT(data_type(length),expression,style)3、逻辑控制语句：顺序结构控制语句：BEGIN-END语句BEGINSql_statementEND分支结构控制语句：IF-ELSE语句和CASE-END语句IF(Boolean-expression)sql_statementELSEsql_statementCASEWHEN条件1THEN结果1WHEN条件2THEN结果2ELSE其他结果END循环控制语句：WHILE语句WHILE(Boolean_expression)BEGINSql_statement|statement_blockBREAK|CONTINUEEND4、批处理：包含一个或多个SQL语句的组，从应用程序一次性地发送到SQLServer执行，GO是批处理的标志，GO命令和SQL语句不能在同一行里，必须单起一行，表示SQLServer将这些T-SQL语句编译为一个执行单元，提高执行效率：SQLServer规定，如果是建库、建表语句、以及存储过程和视图等，必须在语句末尾添加添加GO批处理标志5、联合查询6、子查询：SELECT（UPDATE、INSERT、DELETE）FROM表1WHERE列1运算符(子查询)，嵌套查询先从最内层子查询开始分析,子查询可以嵌套在SQL语句中任何表达式出现的位置子查询语句必须放置在括号内，将子查询与比较运算符联合使用，必须保证子查询返回的值不能多于一个SELECTTraineeNo,TraineeName,Sex,BornDate,AddressFromTraineeWHEREBornDate(SELECTBornDateFROMTraineeWHERETraineeName=Lily)IN、NOTIN子查询：IN后面的子查询可以返回多条记录，常用IN替换等于（=）的比较子查询SELECTTraineeNameFROMTraineeWHERETraineeNoIN(SELECTTraineeNoFROMResultWHERESubjectNo=(SELECTSubjectNoFROMSubjectWHERESubjectName=SQLServer)ANDTraineeResult)EXISTS：常用与IFEXISTS（子查询），结果非空则EXISTS返回true,否则返回false相关子查询：父查询对子查询产生影响ALL、ANY、SOME子查询：ALL:父查询中大于子查询中所有记录SELECTSubjectName科目名称,ClassHour学时FROMSubjectWHEREClassHuorALL(SELECTAVG(ClassHour)FROMSubjectGROUPBYGradeId)SOME、ANY：父查询中大于子查询中任意一条记录即可SELECTSubjectName科目名称,ClassHour学时FROMSubjectWHEREClassHuorALL(SELECTAVG(ClassHour)FROMSubjectGROUPBYGradeId)8、事务处理事务：一系列的人物组成的逻辑工作单元，这个逻辑单元中的所有任务必须作为一个整体要么全部完成，要么全部失败，保证数据完整性和数据可恢复性。事务特性：原子性，事务的各步操作是不可分的；一致性，但事务完成时，数据必须处于一致状态；隔离性，事务必须是独立的，不应以任何方式依赖于或影响其他事务；永久性，事务完成后，他对数据库的修改被永久保持，事务日志能够保持事务的永久性；开始事务：BEGINTRANSACTION；提交事务：COMMITTRANSACTION；回滚事务：ROLLBACKTRANSACTION；编写事务原则：事务尽量简短；事务访问的数据量尽量最少；查询数据时尽量不要使用事务；在事务处理过程中尽量不要出现等待用户输入的操作嵌套事务：事务的内部可以包含其他事务；忽略内层事务的COMMIT语句，只提交外层事务的COMMIT语句；事务的分类：显式事务；隐式事务；自动提交事务锁与事务：五、视图与索引1、视图：将多个物理数据表抽象为一个逻辑数据表，命名规范：V_视图名，创建视图名：CREATEVIEW视图名ASSELECTstudentNo，stuNameFROMWHEREGrade=1WITHCHECKOPTION，强制针对视图执行的修改都必须符合查询语句中设置的条件。删除视图，DROPVIEW视图名；更新视图，ALTERVIEW视图名；加密视图：CREATEVIEW视图名WITHENCRYPTIONASSELECTstudentNo，stuNameFROMWHEREGrade=1WITHCHECKOPTION基本原则：2、索引：提高查询性能，但是影响插入操作性能聚集索引：索引的键值的逻辑顺序决定了表中相应行的物理顺序，一个表中只能有一个聚集索引；适用于范围查询，使用运算符（如BETWEEN、0beginprint-该级别下开设科目信息如下-selectGradeName,SubjectName,ClassHourfromGradeleftjoinsubjectonGrade.GradeId=Subject.GradeIDwhereGradeName=GradeNameselectCourseNum=COUNT(SubjectNo),HourNum=SUM(ClassHour)fromGradeinnerjoinSubjectonGrade.GradeID=Subject.GradeIDwhereGradeName=GradeNamereturn1dropprocusp_query_subjectendelsegoreturn注意事项：在存储过程中，可以包含CREATETABLE语句，但不能包含CREATEPROCEDURE和CREATEVIEW语句；在存储过程中可以声明和使用局部变量；在存储过程中，可以创建和使用临时表；存储过程可以嵌套调用，被调用的存储过程可以使用调用的存储过程中的对象。存储过程的安全与性能优化主要是扩展存储过程，如xp_cmdshell存储过程，可以执行系统命令编写安全扩展存储过程新建类库程序然后，将类库文件拷贝到安装目录的VC目录下,使用命令提示生成密钥文件输入：snkhelperkey.snk(密钥名)编译类库文件，生成动态链接库，输入：csc/t:library/keyfile:helperkey.snkExtendPro.cs（类库名称）注册动态链接库，输入：regasm/tlb:ExtendPro.tlbExtendePro.dll/codebase生成.tlb文件在SQLServer中利用OLE自动化存储过程调用dll文件首先，创建对象，DeclareObjectint-返回创建的对象Declarehrint-过程返回值Declareretrurnvarchar(255)-dll方法返回值Declaresrcvarchar(255),descvarchar(255)-过程的错误原因、描述Exechr=sp_OACreate命名空间.类名,ObjectoutputIfhr0BeginExecsp_OAGetErrorInfoObject,srcoutput,descoutputSelecthr=convert(varbinary(4),hr),Source=src,Description=descreturnend调用方法，Exechr=sp_OAMethodObject,方法名,descoutputIfhr0BeginExecsp_OAGetErrorInfoObject,srcoutput,descoutputSelecthr=convert(varbinary(4),hr),Source=src,Description=descreturnend销毁实例Exechr=sp_OADestroyObjectIfhr0BeginExecsp_OAGetErrorInfoObject,srcoutput,descoutputSelecthr=convert(varbinary(4),hr),Source=src,Description=descreturnend启用与禁用ole自动化存储过程的方法(第二个参数为1时启用，为0时禁用，禁用时，要先保持Sp_configureshowadvancedoptions,1的状态，禁用Sp_configureoleautomationprocedures,0;)Sp_configureshowadvancedoptions,1GoReconfigureGoSp_configureoleautomationprocedures,1GoReconfigurego创建加密存储过程语法：createprocwithencryptionas七、触发器触发器时数据库服务器发生事件时，自动执行的特殊存储过程。作用：强制业务规则；强化约束；跟踪变化；级联运行种类：DML触发器，包括对表或视图发出update、insert或delete语句，又分为after触发器和insteadof触发器；DDL触发器，主要是以create、alter和drop开头的语句修改和删除：alter语句和drop语句1、创建after触发器：createtrigger触发器名称on表名for（after）delete，insert，updateassql语句例1、删除：createtriggerEmployee_DeleteonEmployeefordeleteasinsertintoEmployeeOLDselect*fromdeleted例2、更新：CreatetriggerBank_UpdateOnBankForupdateAs(ifupdate()可以检测列是否被修改)DeclarebeforeMoneyMoney,afterMoneyMoneySelectbeforeMoney=CurrentMoneyfromdeletedSelectafterMoney=CurrentMoneyfrominsertedIfABS(afterMoney-beforeMoney)201*0BeginRollbacktranEnd例3、插入：CreatetriggerBorrow_InsertOnBorrowForinsertAsDeclarecountintSelectcount=BookCountfromBookWhereBookId=(selectBookIdfromInserted)Ifcount0UpdateBooksetBookCount=BookCount-1WhereBookId=(selectBookIdfrominserted)ElseRollbacktran2、创建insteadof触发器，适用于表或视图：createtrigger触发器名on表名insteadofdelete，insert，updateassql语句例：createtriggervw_Book_Borrow_Insteadof_DeleteOnvw_Book_BorrowInsteadofdeleteAsDeletefromBorrowBookIdin(selectBookIdfromdeleted)3、DML触发器加密：createtrigger触发器名onwithencryption表名4、DDL触发器：特殊触发器，他是在响应数据定义语言（DDL）语句时触发，仅在DDL语句后才会触发，无法作为insteadof触发器使用DDL语句：create、alter、drop语法：createtrigger触发器名on(allserver或database)for表名assql语句九、数据库设计设计步骤：需求分析阶段；概要设计阶段；详细设计阶段实体关系模型：实体、属性、关系、映射基数（一对一、一对多、多对多）、实体关系图（E-R图）三大范式：第一范式：确保每列的原子性，每列都是不可再分的最小数据单元第二范式：必须满足第一范式，目标是确保表中的每列都和主键相关第三范式：满足第二范式，主键外的其他列和主键直接相关优化数据库结构：分离用户数据库与系统数据库；创建数据库表分区；创建垂直分表（分离大文本字段）；创建水平分表