开发部数据库及数据操作培训资料.ppt

数据库及数据操作培训资料 演示者 施运渊 培训大纲 第1课 关系型数据库简单介绍关系型数据库及常用关系型数据库的介绍 第2课 表表和字段的命名规范 字段数据类型 建表原则等 第3课 视图视图命名规范 索引视图 可更新视图 第4课 索引索引创建原则 哪些情况可能无法使用到索引 第5课 触发器触发器简单说明 使用情况 培训大纲 第6课 存储过程存储过程说明 使用情况 第7课 常用函数一些常用的函数 第8课 性能优化如何着手优化性能 第8课 常用技巧开发时使用到的一些技巧 第1课 关系型数据库 关系型数据库 关系数据库是建立在集合代数基础上 应用数学方法来处理数据库中的数据 现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示 关系模型由关系数据结构 关系操作集合 关系完整性约束三部分组成 关系型数据库以行和列的形式存储数据 以便于用户理解 储存与管理数据的基本形式是二维表 使用结构化查询语言 SQL 作为查询工具 第1课 关系型数据库 常用关系型数据库 OracleSQLServerSybaseDB2MySQL 第2课 数据表 表的定义表 TABLE 是数据库中用来存储数据的对象 是有结构的数据的集合 是整个数据库系统的基础 表命名规则表名用T 开头 表名长度不能超过30个字符 表名中含有单词全部采用单数形式 单词要大写 多个单词间用下划线 进行连接 若库中有多个系统 表名采用系统名称 单词或多个单词 系统名是开发系统的缩写 如VNET 表中含有的单词建议用完整的单词 如果导致表名长度超过30个字符 则从最后一个单词开始 依次向前采用该单词的缩写 第2课 数据表 字段命名规则字段名长度不能超过30个字符 字段名中含有单词全部采用单数形式 单词要大写 多个单词间用下划线 进行连接 字段名中含有的单词建议用完整的单词 如果导致长度超过30个字符 则从最后一个单词开始 依次向前采用该单词的缩写字段类型设置原则按实际业务需要来选择数据类型 例如需要参与计算的就用数字型 与时间有关的就用日期型 切勿所有字段全设置成一种类型 数据表的关联字段必须是同种类型 字段长度也需与业务联系 禁止盲目设置为最大值 第2课 数据表 常用字段类型字符型 Varchar 用来存放可变长度的字符串 一般最大长度在2000 4000左右 绝大部分字符串均可使用此类型 推荐使用 Char 定长的字符串 长度同Varchar Text Clob 用来存放可变长度的字符串 几乎无上限 LONG Oracle专有 超长字符串 但不支持Like 并且每张表只能有1个LONG型字段 准备废弃的字段类型 不推荐 总结 在大部分情况下优先使用Varchar类型 因为其适应性最好 Char可使用在长度范围变化较小的情况下 因为其效率较高 但是由于是定长的字段 所以不足部分会以空格填充 编程时要特别注意 并且占用空间也较大 Nvarchar和Nchar与环境变量NLS指定的语言集密切相关 所以不推荐 第2课 数据表 常用字段类型数字型 Integer 整数型 4字节 值为 2 31 2 31 1Decimal p s 数字数据 固定精度为P 宽度为S 优先使用FLOAT 浮点数类型 NUMBER 38 双精度 运算可能会产生问题 REAL 实数类型 NUMBER 63 精度更高总结 在大部分情况下优先使用Decimal p s 类型 因为其适应性最好 Integer用于确认数据为整数情况下 第2课 数据表 常用字段类型日期型 Datetime 描述某天的日期和时刻 二进制型 Image Blob 存储二进制数据 LongRAW Oracle独有 准备废弃的字段类型 不推荐 第2课 数据表 主键 外键 非空字段 默认数据及备注主键 所有的表必须要有主键 如果没有业务逻辑上的主键 也需要创建一个无意义列作为主键 建议使用一个字段作为主键 可提高索引的大小及效率 外键 没有必要的话不推荐设置外键 校验回滚等操作由程序来控制 过多使用外键只会增加系统的复杂度 非空字段 非空字段可以在数据层面对业务数据进行校验 校验工作主要还是在程序里完成 非空字段只是作为最后的检验手段 默认数据 数字型 日期型建议尽可能设置默认值 可以减少因为数据原因而引起的程序错误 备注 所有字段都必须填写备注 建表原则哈哈 第2课 数据表 建表原则与实体相关 也就是说一张表能对应现实世界的一种事物在某一方面的属性或一种事务在某一方面的属性 简单来说一个员工 事物 其基本信息就应该是独立的一张表 一次员工的薪资调整 事务 也是独立的一张表 正确理解一对一 一对多 多对多的关系 一对一 用户的通讯方式 包括手机 居住地址等等 与用户的基本信息 姓名 性别 等就是一对一的关系 建议将一对一的数据存储在一张表内 增加表的长度而减少表的数量 一对多 用户基本信息与用户履历就是一对多关系 可使用2张表 用户基本信息表 用户履历表 来存储 多对多 人员与岗位之间就是多对多关系 一般使用3张表来存储这种结构 人员表 岗位表 人员岗位关系表 第2课 数据表 建表原则通俗地理解三个范式 通俗地理解是够用的理解 并不是最科学最准确的理解 第一范式 1NF是对属性的原子性约束 要求属性具有原子性 不可再分解 第二范式 2NF是对记录的惟一性约束 要求记录有惟一标识 即实体的惟一性 第三范式 3NF是对字段冗余性的约束 即任何字段不能由其他字段派生出来 它要求字段没有冗余 没有冗余的数据库设计可以做到 但是 没有冗余的数据库未必是最好的数据库 有时为了提高运行效率 就必须降低范式标准 适当保留冗余数据 第2课 数据表 建表原则计算列 列有两种类型 数据列和计算列 数据列指的是原始采样而得的数据 计算列是指对数据列通过一定的公式计算加工而得出的数据列 例如 单价 数量属于数据列 总价 单价 数量就是计算列 根据业务需求 对于常用的计算数据我们可以通过增加冗余的计算列来减少查询的时间 切记计算列需要使用触发器或者程序来保持其正确性 正确认识数据冗余 增加数据冗余的目的是为了减少表关联从而提高检索速度 切记冗余列需要使用触发器或者程序来保持其正确性 在以下情况下可以考虑使用数据冗余 当查询的主表或从表中有一张或多张表数据量巨大 参与关联的表太多 主要业务数据 也就是用户经常查询的数据 第2课 数据表 建表原则三少原则 一个数据库中表的个数越少越好 只有表的个数少了 才能说明系统的E R图少而精 去掉了重复的多余的实体 形成了对客观世界的高度抽象 进行了系统的数据集成 防止了打补丁式的设计 一个表中组合主键的字段个数越少越好 因为主键的作用 一是建主键索引 二是做为子表的外键 所以组合主键的字段个数少了 不仅节省了运行时间 而且节省了索引存储空间 一个表中的字段个数越少越好 只有字段的个数少了 才能说明在系统中不存在数据重复 且很少有数据冗余 更重要的是督促读者学会 列变行 这样就防止了将子表中的字段拉入到主表中去 在主表中留下许多空余的字段 所谓 列变行 就是将主表中的一部分内容拉出去 另外单独建一个子表 第2课 数据表 建表原则三少原则 一个数据库中表的个数越少越好 只有表的个数少了 才能说明系统的E R图少而精 去掉了重复的多余的实体 形成了对客观世界的高度抽象 进行了系统的数据集成 防止了打补丁式的设计 一个表中组合主键的字段个数越少越好 因为主键的作用 一是建主键索引 二是做为子表的外键 所以组合主键的字段个数少了 不仅节省了运行时间 而且节省了索引存储空间 一个表中的字段个数越少越好 只有字段的个数少了 才能说明在系统中不存在数据重复 且很少有数据冗余 更重要的是督促读者学会 列变行 这样就防止了将子表中的字段拉入到主表中去 在主表中留下许多空余的字段 所谓 列变行 就是将主表中的一部分内容拉出去 另外单独建一个子表 第3课 视图 视图的作用视图能简化用户的操作 主要功能 视图机制可以使用户以不同的方式查询同一数据 视图对数据库重构提供了一定程度的逻辑独立性 视图可以对机密的数据提供安全保护 命名规则名称用VW 开头 长度不能超过30个字符 名称中含有单词全部采用单数形式 单词要大写 多个单词间用下划线 进行连接 名称中含有的单词建议用完整的单词 如果导致长度超过30个字符 则从最后一个单词开始 依次向前采用该单词的缩写 第3课 视图 索引视图对于标准视图而言 为每个引用视图的查询动态生成结果集的开销很大 特别是对于那些涉及对大量行进行复杂处理 如聚合大量数据或联接许多行 的视图 如果在查询中频繁地引用这类视图 可通过对视图创建唯一聚集索引来提高性能 对视图创建唯一聚集索引后 结果集将存储在数据库中 就像带有聚集索引的表一样 如果很少更新基础数据 则索引视图的效果最佳 维护索引视图的成本可能高于维护表索引的成本 索引视图可以提高下列查询类型的性能 处理大量行的联接和聚合 许多查询经常执行的联接和聚合操作 决策支持工作负荷 第3课 视图 索引视图索引视图通常不会提高下列查询类型的性能 具有大量写操作的OLTP系统 具有大量更新的数据库 不涉及聚合或联接的查询 GROUPBY键具有高基数度的数据聚合 GROUPBYID 语法 CREATETABLEwide tbl aintPRIMARYKEY bint zint CREATEVIEWv abcWITHSCHEMABINDINGASSELECTa b cFROMdbo wide tblWHEREaBETWEEN0AND1000CREATEUNIQUECLUSTEREDINDEXi abcONv abc a 第3课 视图 可更新视图利用视图进行数据增 删 改操作 会受到一定的限制 由两个以上的基本表导出的视图 视图的字段来自字段表达式函数 视图定义中有嵌套查询 在一个不允许更新的视图上定义的视图 第4课 索引 定义说明使用索引可快速访问数据库表中的特定信息 索引是对数据库表中一列或多列的值进行排序的一种结构 例如employee表的姓 lname 列 如果要按姓查找特定职员 与必须搜索表中的所有行相比 索引会帮助您更快地获得该信息 索引是一个单独的 物理的数据库结构 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单 索引提供指向存储在表的指定列中的数据值的指针 然后根据您指定的排序顺序对这些指针排序 数据库使用索引的方式与您使用书籍中的索引的方式很相似 它搜索索引以找到特定值 然后顺指针找到包含该值的行 第4课 索引 索引的优点大大加快数据的检索速度 创建唯一性索引 保证数据库表中每一行数据的唯一性 加速表和表之间的连接 在使用分组和排序子句进行数据检索时 可以显著减少查询中分组和排序的时间 索引的缺点索引需要占物理空间 当对表中的数据进行增加 删除和修改的时候 索引也要动态的维护 降低了数据的维护速度 错误的索引可能会减慢查询速度 第4课 索引 索引类型唯一索引 唯一索引是不允许其中任何两行具有相同索引值的索引 主键索引 在数据库关系图中为表定义主键将自动创建主键索引 主键索引是唯一索引的特定类型 该索引要求主键中的每个值都唯一 当在查询中使用主键索引时 它还允许对数据的快速访问 聚集索引 在聚集索引中 表中行的物理顺序与键值的逻辑 索引 顺序相同 一个表只能包含一个聚集索引 非簇集索引 普通索引 第4课 索引 建索引原则经常做为条件查询的字段加上索引 如果几个字段同时做为查询时就建复合索引 不要索引常用的小型表 数据量小的表不需要索引 500行 不要在大字段上建立索引如文本型之类的 对于查询中很少涉及的列或者重复值比较多的列 不要建立索引 对于按范围查询的列 最好建立索引 如交易日期等 表中若有主键或者外键 一定要为其建立索引 在SQL语句中经常进行GROUPBY ORDERBY的字段上建立索引 不要过度索引 修改表时数据库需要更新索引 过度的索引不但耗费额外的空间 而且降低了修改更新操作的效率 第4课 索引 无法使用到索引的一些情况当WHERE条件中的字段是类似column1isnull或者column1isnotnull是 即便column1上面本来有索引也不会用到 对列进行数据加工后再做比较 例如 column1 column2 aaabbb Substr column1 1 aaa 等Like的字符串中第一个字符如果是 则用不到索引 例如 Column1like aaa 是可以的Column1like aaa 用不到也用不到索引有时可以采取适当措施改写后可以用到索引 例 column1有3个值 A B C 三个值的分布为 A 10 B 80 C 10 则column1 B 可以改写为column1in A C 第4课 索引 无法使用到索引的一些情况如果能不用到排序 则尽量避免排序 用到排序的情况有UNION 可用UNIONALL操作符替代UNION 因为UNIONALL操作只是简单的将两个结果合并后就返回 OrderbyGroupbyDistinctIn有时候也会用到排序确实要排序的时候也尽量要排序小数据量 尽量让排序在内存中执行 第5课 触发器 定义说明触发器 trigger 是个特殊的存储过程 它的执行不是由程序调用 也不是手工启动 而是由事件来触发 比如当对一个表进行操作 insert delete update 时就会激活它执行 触发器经常用于加强数据的完整性约束和业务规则等 财务 人事等系统较多使用 优点触发器可通过数据库中的相关表实现级联更改 预编译 已优化 效率较高 避免了SQL语句在网络传输然后再解释的低效率 安全 不会有SQL语句注入问题存在 不需要修改程序重新编译 第5课 触发器 缺点可移植性差 触发器排错困难 而且数据容易造成不一致 后期维护不方便 影响数据库的结构 同时增加了维护的复杂程序 语法create orreplace trigger触发器名触发时间触发事件on表名 foreachrow 第5课 触发器 触发时间 指明触发器何时执行before 表示在数据库动作之前触发器执行 after 表示在数据库动作之后出发器执行 触发事件 指明哪些数据库动作会触发此触发器insert 数据库插入会触发此触发器 update 数据库修改会触发此触发器 delete 数据库删除会触发此触发器 表名 数据库触发器所在的表 foreachrow 对表的每一行触发器执行一次 如果没有这一选项 则只对整个表执行一次 第5课 触发器 触发器无法使用的语句CREATE语句 如 CREATETABLE CREATEINDEX等 ALTER语句 如 ALTERTABLE ALTERINDEX等 DROP语句 如 DROPTABLE DROPINDEX等 第6课 存储过程 定义说明存储过程 StoredProcedure 是一组为了完成特定功能的SQL语句集 经编译后存储在数据库中 用户通过指定存储过程的名字并给出参数 如果该存储过程带有参数 来执行它 存储过程是数据库中的一个重要对象 任何一个设计良好的数据库应用程序都应该用到存储过程 优点预编译 已优化 效率较高 避免了SQL语句在网络传输然后再解释的低效率 安全 不会有SQL语句注入问题存在 不需要修改程序重新编译 第6课 存储过程 缺点可移植性差 调试困难 影响数据库的结构 同时增加了维护的复杂程序 语法CREATEPROCEDURE 拥有者 存储过程名 程序编号 参数 1 参数 1024 WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION AS程序行 第7课 常用函数 数字函数没有特别特殊的 大家可以参考手册 字符串函数字符串连接符Oracle Sybase SQLServer MySQL 无字符串连接函数 CONCAT str1 str2 str3 INSTR C1 C2 I J 在一个字符串中搜索指定的字符 返回发现指定的字符的位置 C1被搜索的字符串C2希望搜索的字符串 第7课 常用函数 字符串函数LENGTH 返回字符串的长度LOWER 返回字符串 并将所有的字符小写UPPER 返回字符串 并将所有的字符大写LTRIM和RTRIM LTRIM删除左边出现的字符串 RTRIM删除右边出现的字符串SUBSTR string start count 取子字符串 从start开始 取count个REPLACE string s1 s2 string希望被替换的字符或变量 s1被替换的字符串 s2要替换的字符串 第7课 常用函数 日期函数当前日期时间 Oracle SysdateSQLServer Sybase GetDate MySql Now curedate 等等 不同的是这些都不能作为字段默认值 默认值必须使用current timestamp 但是current timestamp只用在timestamp的列 对datetime列无效 日期转换函数 Oracle TO CHAR 日期字段 YYYY MM DDHH24 MI SS TO DATE 日期格式字符串 该字符串的格式 SQLServer Sybase 字符串可以直接当做日期来插入 格式化获取日期用Convert Char 长度 日期字段 参数 Char代表获取的字符长度 参数有很多 常用20 108 120 111等 第7课 常用函数 日期函数两个日期之差 Oracle 日期1 日期2 返回带小数点的天数SQLServer Sybase DATEDIFF datepart startdate enddate datepart 日期差的单位 MySql DATEDIFF startdate enddate 返回带小数点的天数日期添加时间间隔 Oracle 时间函数 天数 可以带小数 SQLServer Sybase DATEADD datepart Number 日期字段 datepart 日期单位 Number 增加的数字 可以为负 MySQL DATE ADD date INTERVALexprtype 第8课 性能优化 在给定的系统硬件和系统软件条件下 提高数据库系统的运行效率的办法是 1 在数据库物理设计时 降低范式 增加冗余 少用触发器 多用存储过程 2 当计算非常复杂 而且记录条数非常巨大时 例如一千万条 复杂计算要先在数据库外面 以文件系统方式用C 语言计算处理完成之后 最后才入库追加到表中去 3 发现某个表的记录太多 例如超过一千万条 则要对该表进行水平分割 水平分割的做法是 以该表主键PK的某个值为界线 将该表的记录水平分割为两个表 若发现某个表的字段太多 例如超过八十个 则垂直分割该表 将原来的一个表分解为两个表 第9课 常用技巧 SQL语句性能一个SQL语句不要关联太多的表 如果其中有大数据量的表时 尽量减少该表与其他表的关联 Where条件禁用或少用函数及字段数据操作 因为这样无法使用到索引 例如SELECT FROMAwhereprice num 1000 Where子句顺序对性能没有影响 可能一些老版本的DBMS会有影响 为了统一 一般来说表之间的连接必须写在其他WHERE条件之前 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾 SELECT子句中避免使用 ORACLE在解析的过程中 会将 依次转换成所有的列名 这个工作是通过查询数据字典完成的 这意味着将耗费更多的时间 第9课 常用技巧 SQL语句性能SELECT子句禁用或少用Distinct Distinct会引起全表扫描一般可以考虑用EXIST替换 EXISTS使查询更为迅速 因为RDBMS核心模块将在子查询的条件一旦满足后 马上返回结果 例子 低效 SELECTDISTINCTDEPT NO DEPT NAMEFROMDEPTD EMPEWHERED DEPT NO E DEPT NO 高效 SELECTDEPT NO DEPT NAMEFROMDEPTDWHEREEXISTS SELECT X FROMEMPEWHEREE DEPT NO D DEPT NO sql语句用大写的 因为oracle总是先解析sql语句 把小写的字母转换成大写的再执行 第9课 常用技巧 SQL语句性能用EXISTS替代IN 用NOTEXISTS替代NOTIN 在许多基于基础表的查询中 为了满足一个条件 往往需要对另一个表进行联接 在这种情况下 使用EXISTS 或NOTEXISTS 通常将提高查询的效率 在子查询中 NOTIN子句将执行一个内部的排序和合并 无论在哪种情况下 NOTIN都是最低效的 因为它对子查询中的表执行了一个全表遍历 为了避免使用NOTIN 我们可以把它改写成外连接 OuterJoins 或NOTEXISTS 例子 高效 SELECT FROMEMP 基础表 WHEREEMPNO 0ANDEXISTS SELECT X FROMDEPTWHEREDEPT DEPTNO EMP DEPTNOANDLOC MELB 低效 SELECT FROMEMP 基础表 WHEREEMPNO 0ANDDEPTNOIN SELECTDEPTNOFROMDEPTWHERELOC MELB 第9课 常用技巧 SQL语句性能使用表的别名 Alias 当在SQL语句中连接多个表时 请使用表的别名并把别名前缀于每个Column上 这样一来 就可以减少解析的时间并减少那些由Column歧义引起的语法错误 用 替代 高效 SELECT FROMEMPWHEREDEPTNO 4低效 SELECT FROMEMPWHEREDEPTNO 3两者的区别在于 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO 3的记录并且向前扫描到第一个DEPT大于3的记录 避免在索引列上使用ISNULL和ISNOTNULL 第9课 常用技巧 SQL语句性能用UNION替换OR 适用于索引列 通常情况下 用UNION替换WHERE子句中的OR将会起到较好的效果 对索引列使用OR将造成全表扫描 注重 以上规则只针对多个索引列有效 假如有column没有被索引 查询效率可能会因为你没有选择OR而降低 在下面的例子中 LOC ID和REGION上都建有索引 高效 SELECTLOC ID LOC DESC REGIONFROMLOCATIONWHERELOC ID 10UNIONSELECTLOC ID LOC DESC REGIONFROMLOCATIONWHEREREGION MELBOURNE 低效 SELECTLOC ID LOC DESC REGIONFROMLOCATIONWHERELOC ID 10ORREGION MELBOURNE 假如你坚持要用OR 那就需要返回记录最少的索引列写在最前面 第9课 常用技巧 SQL语句性能总是使用索引的第一个列 假如索引是建立在多个列上 只有在它的第一个列 leadingcolumn 被where子句引用时 优化器才会选择使用该索引 这也是一条简单而重要的规则 当仅引用索引的第二个列时 优化器使用了全表扫描而忽略了索引用UNION ALL替换UNION 假如有可能的话 UNION ALL不会去除重复的行 慎用 索引只能告诉你什么存在于表中 而不能告诉你什么不存在于表中 使用count 或count 主键 而不要使用count column name 避免使用count distinctcolumn name 第9课 常用技巧 SQL语句性能等号右边尽量不要使用字段名 如 select fromtbwherefield1 field3尽量少用子查询 特别是相关子查询 因为这样会导致效率下降 使用count 或count 主键 而不要使用count column name 避免使用count distinctcolumn name 第9课 常用技巧 SQL语句调优