SQL高级编程-复习整理.doc

SQL高级编程基础知识大全SQL Server学习期间对课程进行了总结，撰写了本篇基础知识大全，方便大家学习和查阅！第一章 数据库的设计一为什么要规范化优秀的糟糕的数据冗余最低限度的冗余冗余数据过多，浪费严重内存空间有效利用，不浪费内存空间严重浪费，利用率不高网络传输效率高，响应时间短效率低下，响应时间较长数据一致性有保证难以保证，更新和检索会出问题数据完整性有保证难以保证，更新和检索会出问题二数据库设计步骤软件开发生命周期需求分析分析客户业务和数据处理需求概要设计完成数据库的E-R模型图设计（CDM）详细设计将设计好的E-R图转换为表，进行逻辑设计，并最终生成数据库（PDM）数据库设计步骤收集信息创建数据库之前必须充分理解数据需要完成的任务和功能，确定存储内容。标识实体完成收集信息后，就必须将数据库的关键实体标识出来，每个不同的对象都有一个对应的表。标识实体属性实体标识出以后，就需要将该实体的属性进行划分和标识，这些属性将组成列。表示实体关系确定数据库表在逻辑上是如何关联的，然后添加关系并建立起表之间的连接。E-R模型图图形的含义图形矩形（实体）椭圆（属性）菱形（关系）映射基数有哪些基数类型一对一一对多多对一多对多三数据库规范化名称三大范式详解案例1NF列具有原子性（不可再分）（隐含：实体拥有主键列）课程表（学号、姓名、性别、成绩、课程名称）2NF满足1NF的前提下，若主键列只有1列，则自动满足2NF；若主键列有多列时，所有非主键列必须完全依赖于所有主键列。成绩表（学号、姓名、性别、课程编号、课程名称、成绩）3NF满足2NF的前提下，不存在函数传递依赖关系（非主键列必须直接依赖于主键列）学员信息表（学号、姓名、性别系编号、系名称）第二章数据库的实现一创建数据库文件名称后缀名补充主要数据文件mdf数据页大小:8KB次要数据文件ndfNTFS最小单位:簇（8KB）日志文件ldf扩展盘区、8个连续的数据页创建数据库的步骤主数据文件日志文件创建过程USEmasterGOEXECsp_databasesEXECsp_helpdbIfDB_ID(数据库名)0DROP DATABASE 数据库名GOCREATEDATABASE 数据库名 ON(NAME=逻辑名称,FILENAME=物理名称及路径,SIZE=初始大小,MAXSIZE=最大,FILEGROWTH=增长量增长率)LOGON(NAME=逻辑名称,FILENAME=物理名称及路径,SIZE=初始大小,MAXSIZE=最大,FILEGROWTH=增长量增长率)注：文件组在LOG ON前面增加一个FILEGROUP自定义文件组名(NAME=)二创建表创建表的步骤创建过程USE数据库名GO删除建议：先从表，后主表IFOBJECT_ID(表名)0DROP TABLE 表名GO建表建议：先主表，后从表，只写基本属性。CREATETABLE 表名(列名 数据类型长度 NULL/NOT NULL IDENTITY(1,1),)ON文件组GO注意：一个表只能有一个IDENTITY属性列三约束约束名称详细说明实体完整性约束主键约束、唯一约束、标识列域完整性约束限制数据类型、检查约束、默认约束、非空约束参照完整性约束外键约束自定义完整性约束规则、存储过程、触发器约束的写法主键约束ALTER TABLE 表名ADD CONSTRAINT PK_表名_列名 PRIMARY KEY(列名)唯一约束ALTER TABLE 表名ADD CONSTRAINT UQ_表名_列名 unique(列名)外键约束ALTER TABLE 表名ADD CONSTRAINT FK_表名_列名FOREIGN KEY (外键名) REFERENCES 主表名(主键名)检查约束ALTER TABLE 表名ADD CONSTRAINT CK_表名_列名CHECK (条件表达式)默认约束ALTER TABLE 表名ADD CONSTRAINT DF_表名_列名DEFAULT (值) FOR 列名四安全控制数据库安全控制的三层模型认证方式创建登陆账户EXEC sp_addlogin 登陆名,密码EXEC sp_grantlogin 域名用户名创建数据库用户USE 数据库名GOEXEC sp_grantdbaccess登录名,数据库用户名给数据库用户授权USE 数据库名GOGRANTSELECT,INSERT,DELETE,UPDATE ON 对象名 TO 数据库用户名（对象权限）GRANT CREATE TABLE,TO 数据库用户名（语句权限）第三章 T-SQL编程一变量名称说明全局变量开头、服务器端对所有连接可见、仅使用。常见全局变量：error、identity、rowcount、version、fetch_status、trancount、servername 局部变量开头(？)、谁创建谁使用（在当前批内可见）局部变量的声明及赋值declarei intdeclarei int,j intseti=1688selecti=1688,j=999selecti=Age from 表名 where 条件表达式 二输入/输出语句名称说明输出显示SELECT i AS 别名Print 数据类型转换（默认规则：字符串与数值在一起，总会将字符串转换成数值）case(值 as目标类型)convert(目标类型,值)convert(目标类型,值,style)：专用于日期转换 三分支语句名称说明语句块BEGINEND判断语句1IFELSE判断语句2形式1（针对等值判断）：SELECTCASE 列名WHEN 1 THEN aELSE nEND形式2（针对范围判断）：SELECTCASEWHEN 条件表达式 THEN aELSE nEND（CASEEND语句可以用在SELECT、INSERT、UPDATE、DELETE语句中）四While循环名称说明基本语法WHILE (条件表达式)BEGINEND结束循环方式Break /Continue第四章高级查询一子查询子查询相关子查询相关说明子查询中条件判断的值不依赖于父查询相关子查询中的条件判断的值来源于父查询。注意事项IN子查询放回多行一行结果集时使用；所有联接查询均可以用子查询替代；子查询不一定能用联接查询替代。示例SELECTstuNo,stuName FROM stuInfoINNER JOIN stuMarksON stuInfo.stuNO=stuMarks.stuNOSELECTstuNo,stuName FROM stuInfoWHERE EXISTS (SELECT* FROM stuMarks WHERE stuNO=stuInfo.stuNO)SELECTstuNO,stuName FROM stuInfoWHERE stuNO IN(SELECT stuNO FROM stuMarks)SELECTstuNO,stuName FROM stuInfo,stuMarksWHEREstuInfo.stuNO=stuMarks.stuNO二补充名称说明TOPSELECTTOP(1) / SELECT TOP(i) / SELECT TOP(子查询) 取指定条记录：DECLARE num intSET num=4SELECT TOP(1) * FROMBooks WHERE ID NOT IN (SELECT TOP(num-1) ID FROM Books)分页：DECLARE pagesizeint, pageIndex intSET pagesize=10SET pageIndex=1SELECT TOP(pageSize)* FROM booksWHERE ID NOT IN(SELECTTOP(pageSize*(pageIndex-1) ID FROM books)ORDER BYOrder By 列名 / Order By newid() / Order By SUM(列名)ISNULLISNULL(列名,值)三SQL语句编写指南说明指南1一行一列：比较运算符（,等等）一行N列：existsN行N列：existsN行一列：in指南2先确定列；根据列确定表（多列为主）；找表的从表或主表；判断查询类型；写出SQL语句；重复3、4、5知道验证数据结果正确四查询分类单表查询、连接查询、子查询、多表查询、其它（即席查询等）第五章事务、索引和视图一什么是事务【事务】说明为什么需要事务事务是单个的逻辑工作单元，如果某一事务成功，则全部提交，否则全部取消。什么是事务事务的4个属性（ACID）如下原子性（Atomicity）：事务是一个完整的操作，事务的各步操作是不可分的，要么都执行，要么都不执行；一致性（Consistency）：当事务完成时，数据必须处于一致状态；隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何依赖于或影响其他事务；永久性（Durability）：事务完成后，它对数据库的修改将被永久保持，事务日志能够保持事务的永久性。事务的分类显式事务：用Begin Transcation明确指定事务的开始，最常用的事务类型；隐式事务：通过设置SetImplicit_Transcation On语句将隐性事务模式打开；自动提交事务：这是SQL Server的默认模式，将每句SQL都视为一个事务，如果执行成功则自动提交，如果失败则自动回滚。如何创建事务创建事务的语法：DECLARE errorSum INTSET errorSum=0Begin TranscationINSERT/UPDATE/DELETESETerrorSum=errorSum+ERRORIF sum0RollupTranscationELSECommitTranscation二什么是索引【索引】说明什么是索引索引是包含由表或视图中的一列或多列生成的键，这些键存在数据页上。索引的好处索引可以极大提高查询的速度，但是会降低修改数据库的速度。索引的分类唯一索引：不允许2行具有相同的索引键值；主键索引：创建主键时自动创建主键索引，是唯一索引的特殊类型，要求主键中的每个值都是唯一的；聚集索引：键值的罗技顺序决定表中对应行的物理顺序，一张表仅有一个聚集索引；非聚集索引：数据行的物理排序独立于索引排序，一张表最多不得超过249个非聚集索引；组合索引：列多于1列，但最多不能大于16列。如何创建索引创建索引的语法：CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX index_nameON table_name(column_name,column_name)WITH FILLFACTOR=x三什么是视图【视图】说明什么是视图视图是虚拟的表，其内容由SELECT查询语句定义，视图中不存放数据，仅用于显示。同一张原始表，根据不同用户的不同需求，可以创建不同的视图。视图有哪些用途筛选表中的行；防止未经许可的用户访问敏感数据；降低数据库的复杂程度；将多个物理数据库抽象为一个逻辑数据库。如何创建视图创建索引的语法：CREATE VIEW view_nameWITH ENCRYPTIONAS第六章存储过程一什么是存储过程说明什么是存储过程利用SQL Server所提供的TSQL语言编写的程序。存储过程的特征接收输入参数，并向调用过程或语句返回值；包含在数据库中执行操作或调用其他存储过程的编程语句；向调用过程返回状态值，指示执行过程是否成功。存储过程的优势允许模块化设计；允许更快的执行；减少网络流量；可作为安全机制使用。存储过程的分类系统存储过程；扩展存储过程；本地存储过程；临时存储过程；远程存储过程。二有哪些常用的系统存储过程系统存储过程说明sp_databases列出服务器上的所有数据库sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns返回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpIndex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程sp_password添加或修改登陆账户的密码sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本三用户自定义存储过程的方法说明如何创建存储过程创建存储过程的语法：CREATE PROCEDURE 存储过程名参数1 数据类型=默认值OUTPUT,参数n 数据类型=默认值OUTPUTWITH ENCRYPTIONASBEGINSQL语句ENDGO创建存储过程的注意点以及执行顺序建议默认值从右向左依次缺省；输出参数从左向右，存储过程顺序；按参数的位置顺序传递； 例：EXEC 存储过程名值1,值n按参数的名称传递；例：EXEC 存储过程名 参数名1,参数名n注意点建议“按参数的名称传递”；若存在输出参数，调用时必须加“OUTPUT”四自定义错误信息说明判断成功与否的方法EXECresult=存储过程名 参数列表；成功为0，否则为错误的具体编号，编号信息可查阅sys.Messages。存储过程内部可以用Return语句改变返回值含义。自定义错误信息方法Rai