数据库基础ppt课件.pptx

1、数据库基础研讨研发中心2011年11月.听众调研是否学过数据库的课程？都用过什么数据库？使用数据库过程中有什么希望了解的内容？.数据的各种形式纸带/卡片/磁带配置文件数据流持久化对象代码图片/语音/视频.数据库系统的类型关系？非关系关系型数据库系统对象型数据库系统NoSQL存储形式文件数据库系统关系型数据库系统内存数据库系统分布式数据库系统.OLTP/OLAP联机事务处理OLTP（On-Line Transaction Processing）联机分析处理OLAP（On-Line Analytical Processing）OLTPOLTPOLAPOLAP用户用户操作人员,低层管理人员决策人员,

2、高级管理人员功能功能日常操作处理分析决策DB DB 设计设计面向应用面向主题数据数据当前的, 最新的细节的, 二维的分立的历史的, 聚集的, 多维的集成的, 统一的存取存取读/写数十条记录读上百万条记录工作单位工作单位简单的事务复杂的查询用户数用户数上千个上百万个DB DB 大小大小100MB-GB100GB-TB.数据模型的层次数据模型分成两个不同的层次(1) (1) 概念模型概念模型 也称信息模型，它是按用户的观点来对也称信息模型，它是按用户的观点来对数据和信息建模。数据和信息建模。 (2) (2) 数据模型数据模型 主要包括网状模型、层次模型、关系模主要包括网状模型、层次模型、关系模型等

3、，它是按计算机系统的观点对数据建模型等，它是按计算机系统的观点对数据建模。 .数据库设计步骤需求分析概念结构设计数据库运行和维护数据库实施数据库物理设计逻辑结构设计需求说明与数据说明概念结构逻辑结构 物理结构数据库系统.概念模型 E-R图实体-联系图(Entity Relationship Diagram)基本要素是实体型、属性和联系.概念模型-实体间联系类型班级班级-班长班长111:1联系课程选修学生mnm:n联系班级组成学生1n1:n联系.实体间联系类型职工领导1n同一实体型内部的1:n联系课程讲授教师1m多个实体型间的1:n联系参考书n.联系属性的表示方法课程选修学生mn成绩学生学号年龄

4、性别姓名.数据模型层次模型网状模型关系模型.三类完整性约束实体完整性一个关系(就是表格)中不能有重复的行参照完整性当更新、删除、插入一个表中的数据时，通过参照引用关联的另一个表中的数据用户定义的完整性反映应用领域需要遵循的约束条件，体现了具体领域中的语义约束.逻辑范式1NF：属性不可分割2NF：属性完全依赖于主键3NF：属性不依赖于其它非主属性BCNF4NF5NF6NF.1NF：属性不可分割如果关系模式R的每个关系r的属性都是不可分的数据项，那么就称R是第一范式的模式。简单的说，每一个属性都是原子项，不可分割是关系模式应具备的最起码的条件.1NF：属性不可分割学生编号姓名性别联系方式20080

5、901张三男email:,phone:8888666620080902李四女email:,phone:66668888.1NF：属性不可分割学生编号姓名性别电子邮件电话20080901张三男8888666620080902李四女66668888学生编号姓名性别联系方式20080901张三男email:,phone:8888666620080902李四女email:,phone:66668888.2NF：属性完全依赖于主键首先要满足第一范式每个非主属性是由整个主键函数决定的，而不能由主键的一部分来决定不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一

6、个新的实体，新实体与原实体之间是一对多的关系.2NF：属性完全依赖于主键学生课程教师教师职称教材教室上课时间李四Spring张老师java讲师Spring深入浅出3018:00张三Struts杨老师java讲师Struts in Action30213:30主键是：学生和课程.2NF：属性完全依赖于主键学生课程教师教师职称 教室上课时间李四Spring 张老师 java讲师3018:00张三Struts 杨老师 java讲师30213:30课程教材SpringSpring深入浅出StrutsStruts in Action主键是：学生和课程学生课程教师教师职称教材教室上课时间李四Spring张

7、老师java讲师Spring深入浅出3018:00张三Struts杨老师java讲师Struts in Action30213:30.不符合2NF的后果数据冗余更新异常插入异常删除异常学生课程教师教师职称教材教材教室上课时间李四Spring张老师java讲师Spring深入浅出3018:00张三Struts杨老师java讲师Struts in Action30213:30.3NF3NF：属性不依赖于其它非主属性满足第二范式非主属性之间不存在函数依赖由于满足了第二范式，表示每个非主属性都函数依赖于主键。如果非主属性之间存在了函数依赖，就会存在传递依赖，这样就不满足第三范式.3NF：属性不依赖于其

8、它非主属性学生课程教师教师职称教室上课时间李四Spring张老师java讲师3018:00张三Struts杨老师java讲师30213:30.3NF：属性不依赖于其它非主属性学生课程教师教室上课时间李四Spring 张老师3018:00张三Struts 杨老师30213:30教师教师职称张老师java讲师杨老师java讲师“教师职称”依赖于“教师”学生课程教师教师职称教室上课时间李四Spring张老师java讲师3018:00张三Struts杨老师java讲师30213:30.BC范式满足第三范式所有非主键属性都必须依赖于主键不存在任何字段对任一主键字段的传递函数依赖.BCNF范式假设仓库管理

9、关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：(仓库ID, 存储物品ID) (管理员ID, 数量)(管理员ID, 存储物品ID) (仓库ID, 数量)所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：(仓库ID) (管理员ID)(管理员ID) (仓库ID)即存在关键字段决定关键字段的情况，因此不符合BCNF可以把仓库管理

10、关系表分解为二个关系表：仓库管理：StorehouseManage(仓库ID, 管理员ID)；仓库：Storehouse(仓库ID, 存储物品ID, 数量)。.不符合BCNF的问题删除异常：当仓库被清空后，所有”存储物品ID”和”数量”信息被删除的同时，”仓库ID”和”管理员ID”信息也被删除了。插入异常：当仓库没有存储任何物品时，无法给仓库分配管理员。更新异常：如果仓库换了管理员，则表中所有行的管理员ID都要修改。.范式越高越好？优点避免数据冗余减少数据存储空间减轻维护数据完整性的麻烦缺点操作困难，需要联系多个表才能得到所需要数据范式越高性能可能会越差权衡以范式作为依据判断数据模型的问题是否

11、改为更高范式需要经验和实验判断.DN地域树状结构 地域表国家省市地区中国北京海淀区朝阳区河北廊坊保定石家庄IDID名称名称级别级别父父IDID终端数终端数1中国1Null22北京2133河北2134海淀区3255朝阳区3266廊坊3377保定3388石家庄339.DN统计廊坊【6】的终端总数：select sum(终端数) from 地域表 where ID=【6】统计北京【2】的终端总数：select sum(终端数) from 地域表 where ID=【2】or 父ID=【2】统计中国【1】的终端总数：select sum(终端数) from 地域表 where ID=【1】or 父ID

12、=【1】 or 父ID in (select ID from 地域表 where 父ID=【1】)统计任一地域的终端总数的通用算法？.DNSelect sum(终端数) from 地域表 where DN like 【顶级DN】%其中可以为：廊坊【1|3|6|】、北京【1|2|】、中国【1|】IDID名称名称级别级别父父IDIDDNDN终端数终端数1中国1Null1|22北京211|2|33河北211|3|34海淀区321|2|4|55朝阳区321|2|5|66廊坊331|3|6|77保定331|3|7|88石家庄331|3|8|9.数据类型各种数字各种字符串各种时间自增量空值建议尽量使用标准

13、类型，便于移植使用前查手册，确定类型的具体含义.各种数字Int /Smallint/ Mediumint/Tinyint/BigintBit(SQL Server)/boolean(PostgreSQL)Decimal/Numeric/Float/Double/MoneyRowID(Oracle)Uniqueidentifier(SQL Server)：GUIDEnum/Set(Mysql).各种字符串char和varchar的区别定长和变长，效率和空间varchar(n) 和varchar(max) (SQL Server2005)varchar 和 nvarchar 的区别(SQL Ser

14、ver)按字节存储和按unicode字符存储varchar和varchar2(Oracle)oracle增加varchar2类型，保证在任何版本中该数据类型向上和向下兼容varchar是跨数据库兼容BLOG和Text (SQLServer)：大小写敏感和不敏感BLOB和CLOB(Oracle)：二进制数据和字符型数据.时间Date/Time(mysql)DatetimeSmalldatetime(SQL Server)Timestamp.自增量SQLServer：IDENTITY (1, 1)Mysql：int not null auto_incrementAccess：AutoNumberO

15、racle：sequencePostgreSQL：bigserial/serial.空值列属性设置是否可以为空Is null / is not null空串与空值不同在MySQL中，含有空值的列很难进行查询优化.字段命名预先确定命名规范：前缀、是否加下划线避免简单单词，移植时可能会关键字冲突.字段设计数据类型尽量用数字型，数字型的比较比字符型的快很多数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用自增字段要

16、慎用，不利于数据迁移。.SQL基础distinctin/between/is null/likeTop 10/limit 10UNION视图子查询半连接/外连接Group by/max/min/avg/having临时表Where 1=1.子查询select sum(终端数) from 地域表 where ID=【1】or 父ID=【1】 or 父ID in (select ID from 地域表 where 父ID=【1】)Update 地域表 set dn= (select dn from 地域表 as p where 地域表.父ID=p.id)+ID+| Select * into ta

17、ble 北京地域表 from 地域表 where 父ID=2.子查询使用NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法如果保证子查询没有重复，IN、EXISTS的相关子查询可以用INNER JOIN 代替IN的相关子查询用EXISTS代替不要用COUNT(*)的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS.外连接/半连接姓名姓名JavaJava水平水平张三熟练李四一般姓名姓名VCVC水平水平张三一般王五熟练姓名姓名性别性别张三男李四男王五男赵六女内连接：select * from java, VC where java.姓名=vc.姓名 结

18、果中只有张三外连接：select * from java outer join VC on java.姓名=vc.姓名 结果中有张三、李四、王五左连接：select * from java left join VC on java.姓名=vc.姓名 结果中有张三、李四右连接：select * from java right join VC on java.姓名=vc.姓名 结果中有张三、王五多个左连接：select * from 人员 left join java on 人员.姓名=java.姓名 left join vc on 人员.姓名=vc.姓名【人员】表 【java】表 【VC】表.G

19、roup by havingSelect 父ID, sum(终端数), count(*) from 地域group by 父IDhaving count(*)2IDID名称名称级别级别父父IDIDDNDN终端数终端数1中国1Null1|22北京211|2|33河北211|3|34海淀区321|2|4|55朝阳区321|2|5|66廊坊331|3|6|77保定331|3|7|88石家庄331|3|8|9.临时表会话中断时，临时表自动消失SQLServerCreate table #tmptable1();Oracle:Create global temporary table tmptable2

20、;.临时表的使用如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据其他情况下，应该控制临时表和表变量的使用.临时表的使用关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。关于临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，我们做过测试，一般情况下，SE

21、LECT INTO会比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。注意排序规则，用CREATE TABLE建立的临时表，如果不指定字段的排序规则，会选择TEMPDB的默认排序规则，而不是当前数据库的排序规则。如果当前数据库的排序规则和TEMPDB的排序规则不同，连接的时候就

22、会出现排序规则的冲突错误。一般可以在CREATE TABLE建立临时表时指定字段的排序规则为DATABASE_DEFAULT来避免上述问题.Where 1=1便于拼接SQL字符串Select * from table1where 1=1 and regionname=“=“北京北京” and tp=“2” and tp=“2”.WHERE子句问题对数据字段做运算影响性能对数据字段使用函数影响性能不要滥用OR运算符对于连续的数值，应使用Between替换in.不要对数据字段做运算无运算的字段可以引用索引，有运算的字段将无法引用索引进行优化而需要扫描整个表示例比较下列语法的差异：SELECT *

23、FROM Order Details WHERE Quantity=100SELECT * FROM Order Details WHERE Quantity+1 =101还包括其它的运算，如字符连接等.不要使用负向查询负向查询：NOT、!=、!、NOT EXISTS、NOT IN、NOT LIKE等负向查询不能充分利用索引进行二分查找，需要扫描整张表示例SELECT * FROM Order Details WHERE Quantity!=100可改成：SELECT * FROM Order Details WHERE Quantity100 OR Quantity100.不对数据字段使用函

24、数数据字段使用函数就是一种运算，将使效率低比较：SELECT * FROM Order Details WHERE ABS(Quantity-100)99 AND Quantity101SELECT * FROM Employees WHERE SUBSTRING (LastName,1,1)=D SELECT * FROM Employees WHERE LastName LIKE D%.使用OR运算符AND运算符可以充分引用索引SELECT * FROM Orders WHERE CustomerID=IS10008 AND OrderDate=20060808只需要在CustomerID

25、上建索引就可以了OR运算符需要对参与查询的多个字段都建索引，否则将可能扫描全表SELECT * FROM Orders WHERE CustomerID=IS10008 OR OrderDate=20060808需要在CustomerID和OrderDate两个属性上都建合适的索引,否则将扫描整个数据表.主键/外键主键的选择主键与唯一索引外键慎用删除和级联删除.索引基础唯一索引与非唯一索引多列组合索引聚集索引索引的使用.索引设计索引数据放在分页中，用来当做索引的数据字段越小越好，也就是让分页尽量存放更多的索引项根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序

26、的字段作为索引的候选字段。把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。一个表不要加太多索引，因为索引影响插入和更新的速度。.数据加载大量数据加载外键自增量.大量数据大量数据加载大量加载某个数据表时，应考虑先删掉索引，加载完毕再重建索引（特别是多个用户端同时在做大量数据加载时）BULK INSERT语法通常比bcp工具程序快大量数据加载时，应设参数采用数据表锁定，而不要采用默认的记录锁如果数据表的记录需要先做转换，应先导入临时表中，经过处理再大量加载到目的数据表中INSERT、DELETE和UPDATE对大量

27、数据，SELECT INTO比INSERT快对大量数据，TRUNCATE TABLE比DELETE TABLE快UPDATE和DELETE采用WHERE子句时，条件要符合WHERE的有效格式.SQL注入常见注入方法程序中的SQL字符串为：sql=“select * from users where name=”+inputname+”输入的名称字符串为： 黑客;delete * from users where name拼接成的SQL字符串为：select * from users where name=黑客;delete * from users where name如何避免对输入内容进行检

28、查和处理查询条件使用filter属性赋值使用字段分别赋值.锁类型/游标类型锁类型adLockReadOnly 1 只读锁定, 指定不能修改记录集中的记录adLockPrssimistic 2 保护式锁定, 指在编辑一个记录时, 立即锁定它adLockOptimistic 3 开放式锁定, 指定只有调用记录集的 Update() 方法时才能锁定记录，而在此前的其他操作仍可对当前记录进行更改、插入和删除等adLockBatchOptimistic 4编辑时记录不会被锁定，改插删是在批处理方式下完成的游标类型adOpenForwardOnly 0：默认游标类型, 只能在记录集中向前移动不支持分页、r

29、ecordcountadOpenKeyset 1：键集类型的游标, 可以向前或向后移动如果其他用户修改或删除了一条记录, 记录集中将反映这个变化.如果其他用户添加了一条新记录, 新记录不会出现在记录集中adOpenDynamic 2：动态游标, 可以在记录集中向前或向后移动其他用户造成的记录的任何变化都将在记录集中有所反映adOpenStatic 3：静态游标, 可以在记录集中向前或向后移动不会对其他用户造成的记录变化有所反映.数据库优化逻辑范式优化合理冗余主键/外键物理存储和环境设计系统设计编码注意事项.主键主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往

30、往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。在有多个键的表，选择总的长度小的键小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。注意组合主键的字段次序不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。.外键外键很麻烦？外键是最高效的一致性维护方法数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序。一般认为，离数据越近的方法效率越高。谨慎使用级联删除.数据库物理存储和环境的设计在设计阶段，可以对数据库的物理存储、操作系统环境、网络环境进行必要的设计，使得我们的系统在将来能适应比较多的

31、用户并发和比较大的数据量。这里需要注意文件组的作用，适用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力不同对象：数据表、索引等可考虑分散在不同的文件组不同使用模式的数据文件可通过文件组配置到不同的硬盘，如：系统数据库文件、用户数据库频繁操作的多个表以及日志文件可以分开存放到不同硬盘。.编码-只返回需要的数据横向来看，不要写SELECT *的语句，而是选择你需要的字段。纵向来看，合理写WHERE子句，不要写没有WHERE的SQL语句。注意SELECT INTO后的WHERE子句，因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据

32、过多或者速度太慢，会造成系统表长期锁定，阻塞其他进程。对于聚合查询，可以用HAVING子句进一步限定返回的行。.编码-尽量少做重复的工作控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销合并对同一表同一条件的多次UPDATEUPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。.数据库编程技巧数据库连接池Prepare存储过程.双机/多机高可靠性一写多读分表分片.