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

数据库及数据操作培训资料,演示者：施运渊,培训大纲,第 1 课：关系型数据库 简单介绍关系型数据库及常用关系型数据库的介绍。 第 2 课：表 表和字段的命名规范、字段数据类型、建表原则等。 第 3 课：视图 视图命名规范、索引视图、可更新视图。 第 4 课：索引 索引创建原则、哪些情况可能无法使用到索引。 第 5 课：触发器 触发器简单说明、使用情况。,培训大纲,第 6 课：存储过程 存储过程说明、使用情况。 第 7 课：常用函数 一些常用的函数。 第 8 课：性能优化 如何着手优化性能。 第 8 课：常用技巧 开发时使用到的一些技巧。,第 1 课：关系型数据库,关系型数据库。 关系数据库是建立在集合代数基础上，应用数学方法来处理数据库中的数据。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。 关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成。 关系型数据库以行和列的形式存储数据，以便于用户理解。储存与管理数据的基本形式是二维表。 使用结构化查询语言 (SQL)作为查询工具。,第 1 课：关系型数据库,常用关系型数据库。 Oracle SQL Server Sybase DB2 MySQL,第 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字节，值为-231231-1 Decimal (p,s) ：数字数据，固定精度为P，宽度为S 。优先使用 FLOAT：浮点数类型，NUMBER(38)，双精度，运算可能会产生问题。 REAL：实数类型，NUMBER(63)，精度更高 总结：在大部分情况下优先使用Decimal (p,s)类型，因为其适应性最好， Integer用于确认数据为整数情况下。,第 2 课：数据表,常用字段类型 日期型： Datetime ：描述某天的日期和时刻。 二进制型： Image （Blob）：存储二进制数据。 Long RAW：Oracle独有，准备废弃的字段类型，不推荐。,第 2 课：数据表,主键、外键、非空字段、默认数据及备注 主键：所有的表必须要有主键，如果没有业务逻辑上的主键，也需要创建一个无意义列作为主键。建议使用一个字段作为主键，可提高索引的大小及效率。 外键：没有必要的话不推荐设置外键，校验回滚等操作由程序来控制。过多使用外键只会增加系统的复杂度。 非空字段：非空字段可以在数据层面对业务数据进行校验，校验工作主要还是在程序里完成，非空字段只是作为最后的检验手段。 默认数据：数字型、日期型建议尽可能设置默认值，可以减少因为数据原因而引起的程序错误。 备注：所有字段都必须填写备注。 建表原则 哈哈,第 2 课：数据表,建表原则 与实体相关：也就是说一张表能对应现实世界的一种事物在某一方面的属性或一种事务在某一方面的属性。简单来说一个员工（事物）其基本信息就应该是独立的一张表，一次员工的薪资调整（事务）也是独立的一张表。 正确理解一对一、一对多、多对多的关系： 一对一：用户的通讯方式（包括手机、居住地址等等）与用户的基本信息（姓名、性别）等就是一对一的关系，建议将一对一的数据存储在一张表内，增加表的长度而减少表的数量。 一对多：用户基本信息与用户履历就是一对多关系，可使用2张表（用户基本信息表、用户履历表）来存储。 多对多：人员与岗位之间就是多对多关系，一般使用3张表来存储这种结构（人员表、岗位表、人员岗位关系表）。,第 2 课：数据表,建表原则 通俗地理解三个范式 ：通俗地理解是够用的理解，并不是最科学最准确的理解)： 第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解； 第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性； 第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。 没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。,第 2 课：数据表,建表原则 计算列：列有两种类型：数据列和计算列，数据列指的是原始采样而得的数据，计算列是指对数据列通过一定的公式计算加工而得出的数据列。例如：单价、数量属于数据列，总价=单价*数量就是计算列。根据业务需求，对于常用的计算数据我们可以通过增加冗余的计算列来减少查询的时间，切记计算列需要使用触发器或者程序来保持其正确性。 正确认识数据冗余：增加数据冗余的目的是为了减少表关联从而提高检索速度，切记冗余列需要使用触发器或者程序来保持其正确性。在以下情况下可以考虑使用数据冗余： 当查询的主表或从表中有一张或多张表数据量巨大。 参与关联的表太多。 主要业务数据，也就是用户经常查询的数据。,第 2 课：数据表,建表原则 三少原则： 一个数据库中表的个数越少越好。只有表的个数少了，才能说明系统的E-R图少而精，去掉了重复的多余的实体，形成了对客观世界的高度抽象，进行了系统的数据集成，防止了打补丁式的设计。 一个表中组合主键的字段个数越少越好。因为主键的作用，一是建主键索引，二是做为子表的外键，所以组合主键的字段个数少了，不仅节省了运行时间，而且节省了索引存储空间。 一个表中的字段个数越少越好。只有字段的个数少了，才能说明在系统中不存在数据重复，且很少有数据冗余，更重要的是督促读者学会“列变行”，这样就防止了将子表中的字段拉入到主表中去，在主表中留下许多空余的字段。所谓“列变行”，就是将主表中的一部分内容拉出去，另外单独建一个子表。,第 2 课：数据表,建表原则 三少原则： 一个数据库中表的个数越少越好。只有表的个数少了，才能说明系统的E-R图少而精，去掉了重复的多余的实体，形成了对客观世界的高度抽象，进行了系统的数据集成，防止了打补丁式的设计。 一个表中组合主键的字段个数越少越好。因为主键的作用，一是建主键索引，二是做为子表的外键，所以组合主键的字段个数少了，不仅节省了运行时间，而且节省了索引存储空间。 一个表中的字段个数越少越好。只有字段的个数少了，才能说明在系统中不存在数据重复，且很少有数据冗余，更重要的是督促读者学会“列变行”，这样就防止了将子表中的字段拉入到主表中去，在主表中留下许多空余的字段。所谓“列变行”，就是将主表中的一部分内容拉出去，另外单独建一个子表。,第 3 课：视图,视图的作用 视图能简化用户的操作（主要功能）。 视图机制可以使用户以不同的方式查询同一数据。 视图对数据库重构提供了一定程度的逻辑独立性。 视图可以对机密的数据提供安全保护。 命名规则 名称用VW_开头，长度不能超过30个字符，名称中含有单词全部采用单数形式，单词要大写。 多个单词间用下划线（_）进行连接。 名称中含有的单词建议用完整的单词。如果导致长度超过30个字符，则从最后一个单词开始，依次向前采用该单词的缩写,第 3 课：视图,索引视图 对于标准视图而言，为每个引用视图的查询动态生成结果集的开销很大，特别是对于那些涉及对大量行进行复杂处理（如聚合大量数据或联接许多行）的视图。如果在查询中频繁地引用这类视图，可通过对视图创建唯一聚集索引来提高性能。对视图创建唯一聚集索引后，结果集将存储在数据库中，就像带有聚集索引的表一样。 如果很少更新基础数据，则索引视图的效果最佳。维护索引视图的成本可能高于维护表索引的成本。 索引视图可以提高下列查询类型的性能： 处理大量行的联接和聚合。 许多查询经常执行的联接和聚合操作。 决策支持工作负荷。,第 3 课：视图,索引视图 索引视图通常不会提高下列查询类型的性能。 具有大量写操作的 OLTP 系统。 具有大量更新的数据库。 不涉及聚合或联接的查询。 GROUP BY 键具有高基数度的数据聚合。（GROUP BY ID） 语法：CREATE TABLE wide_tbl(a int PRIMARY KEY, b int, ., z int) CREATE VIEW v_abc WITH SCHEMABINDING AS SELECT a, b, c FROM dbo.wide_tbl WHERE a BETWEEN 0 AND 1000 CREATE UNIQUE CLUSTERED INDEX i_abc ON v_abc(a),第 3 课：视图,可更新视图 利用视图进行数据增，删，改操作，会受到一定的限制。 由两个以上的基本表导出的视图。 视图的字段来自字段表达式函数。 视图定义中有嵌套查询。 在一个不允许更新的视图上定义的视图。,第 4 课：索引,定义说明 使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，例如 employee 表的姓（lname）列。如果要按姓查找特定职员，与必须搜索表中的所有行相比，索引会帮助您更快地获得该信息。 索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。,第 4 课：索引,索引的优点 大大加快数据的检索速度。 创建唯一性索引，保证数据库表中每一行数据的唯一性。 加速表和表之间的连接。 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。 索引的缺点 索引需要占物理空间。 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。 错误的索引可能会减慢查询速度。,第 4 课：索引,索引类型 唯一索引：唯一索引是不允许其中任何两行具有相同索引值的索引。 主键索引：在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。 聚集索引：在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。 非簇集索引：普通索引。,第 4 课：索引,建索引原则 经常做为条件查询的字段加上索引，如果几个字段同时做为查询时就建复合索引。 不要索引常用的小型表，数据量小的表不需要索引（500行）。 不要在大字段上建立索引如文本型之类的。 对于查询中很少涉及的列或者重复值比较多的列，不要建立索引。 对于按范围查询的列，最好建立索引，如交易日期等。 表中若有主键或者外键，一定要为其建立索引。 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引。 不要过度索引。修改表时数据库需要更新索引，过度的索引不但耗费额外的空间，而且降低了修改更新操作的效率。,第 4 课：索引,无法使用到索引的一些情况 当WHERE条件中的字段是类似column1 is null 或者column1 is not null是，即便column1上面本来有索引也不会用到。 对列进行数据加工后再做比较。例如： column1|column2=aaabbb Substr(column1,1)=aaa等 Like 的字符串中第一个字符如果是%则用不到索引，例如： Column1 like aaa% 是可以的 Column1 like %aaa%用不到 也用不到索引 有时可以采取适当措施改写后可以用到索引。例：column1 有3个值(A,B,C),三个值的分布为A 10%, B 80%,C 10%,则column1 B 可以改写为column1 in(A,C),第 4 课：索引,无法使用到索引的一些情况 如果能不用到排序，则尽量避免排序。 用到排序的情况有 UNION（可用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回） Order by Group by Distinct In 有时候也会用到排序 确实要排序的时候也尽量要排序小数据量，尽量让排序在内存中执行。,第 5 课：触发器,定义说明 触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等（财务、人事等系统较多使用）。 优点 触发器可通过数据库中的相关表实现级联更改。 预编译，已优化，效率较高。避免了SQL语句在网络传输然后再解释的低效率。 安全。不会有SQL语句注入问题存在。 不需要修改程序重新编译。,第 5 课：触发器,缺点 可移植性差。 触发器排错困难，而且数据容易造成不一致，后期维护不方便。 影响数据库的结构，同时增加了维护的复杂程序。 语法 create or replace trigger 触发器名 触发时间 触发事件 on 表名 for each row,第 5 课：触发器,触发时间：指明触发器何时执行 before-表示在数据库动作之前触发器执行。 after-表示在数据库动作之后出发器执行。 触发事件：指明哪些数据库动作会触发此触发器 insert：数据库插入会触发此触发器； update：数据库修改会触发此触发器； delete：数据库删除会触发此触发器。 表 名：数据库触发器所在的表。 for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。,第 5 课：触发器,触发器无法使用的语句 CREATE 语句，如：CREATE TABLE、CREATE INDEX 等。 ALTER 语句，如：ALTER TABLE、ALTER INDEX 等。 DROP 语句，如：DROP TABLE、DROP INDEX 等。,第 6 课：存储过程,定义说明 存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。 优点 预编译，已优化，效率较高。避免了SQL语句在网络传输然后再解释的低效率。 安全。不会有SQL语句注入问题存在。 不需要修改程序重新编译。,第 6 课：存储过程,缺点 可移植性差。 调试困难。 影响数据库的结构，同时增加了维护的复杂程序。 语法 CREATE PROCEDURE 拥有者.存储过程名;程序编号 (参数#1,参数#1024) WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR REPLICATION 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：Sysdate SQL Server，Sybase：GetDate（）； MySql：Now（），curedate（）等等，不同的是这些都不能作为字段默认值，默认值必须使用current_timestamp，但是current_timestamp只用在timestamp的列，对datetime列无效。 日期转换函数： Oracle：TO_CHAR（日期字段,YYYY/MM/DD HH24:MI:SS）；TO_DATE（日期格式字符串，该字符串的格式） SQL Server，Sybase：字符串可以直接当做日期来插入，格式化获取日期用Convert（Char（长度），日期字段，参数）。Char代表获取的字符长度，参数有很多，常用20，108，120，111等,第 7 课：常用函数,日期函数 两个日期之差： Oracle：日期1-日期2，返回带小数点的天数 SQL Server，Sybase：DATEDIFF ( datepart , startdate , enddate ), datepart :日期差的单位。 MySql： DATEDIFF (startdate , enddate ) ，返回带小数点的天数 日期添加时间间隔： Oracle：时间函数+天数（可以带小数） SQL Server，Sybase： DATEADD(datepart ，Number，日期字段) datepart ：日期单位， Number ：增加的数字，可以为负。 MySQL： DATE_ADD(date,INTERVAL expr type),第 8 课：性能优化,在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是： (1) 在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。 (2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面，以文件系统方式用C+语言计算处理完成之后，最后才入库追加到表中去。 (3) 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。,第 9 课：常用技巧,SQL语句性能 一个SQL语句不要关联太多的表，如果其中有大数据量的表时，尽量减少该表与其他表的关联。 Where条件禁用或少用函数及字段数据操作，因为这样无法使用到索引。例如SELECT * FROM A where price*num1000。 Where子句顺序对性能没有影响，可能一些老版本的DBMS会有影响。为了统一，一般来说表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。 SELECT子句中避免使用 * ： ORACLE在解析的过程中, 会将* 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间,第 9 课：常用技巧,SQL语句性能 SELECT子句禁用或少用Distinct， Distinct会引起全表扫描一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,马上返回结果. 例子： (低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT X FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行,第 9 课：常用技巧,SQL语句性能 用EXISTS替代IN、用NOT EXISTS替代NOT IN： 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.例子： （高效）SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND EXISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB) (低效)SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = MELB),第 9 课：常用技巧,SQL语句性能 使用表的别名(Alias)： 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。 用=替代 高效: SELECT * FROM EMP WHERE DEPTNO =4 低效: SELECT * FROM EMP WHERE DEPTNO 3 两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。 避免在索引列上使用IS NULL和IS NOT NULL,第 9 课：常用技巧,SQL语句性能 用UNION替换OR (适用于索引列) 通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注重, 以上规则只针对多个索引列有效. 假如有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 假如你坚持要用OR, 那就需要返回记录最少的索引列写在最前面。,第 9 课：常用技巧,SQL语句性能 总是使用索引的第一个列： 假如索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引 用UNION-ALL 替换UNION ( 假如有可能的话)： UNION-ALL不会去除重复的行。 慎用“”，索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中。 使用count(*)或count(主键)而不要使用count(column_name)，避免使用count(distinct column_name)。,第 9 课：常用技巧,SQL语句性能 等号右边尽量不要使用字段名，如： select * from tb where field1 = field3 尽量少用子查询，特别是相关子查询。因为这样会导致效率下降。 使用count(*)或count(主键)而不要使用count(column_name)，避免使用count(distinct column_name)。,第 9 课：常用技巧,SQL语句调优方法 SQL执行速度如果不能达到毫秒级，那么在并发的压力下