数据库基本原理及应用.ppt

数据库原理及应用,主要内容,数据库基本概念 数据库 db 数据库管理系统 dbmssql er图 规范化 数据查询 数据操作 表和视图 sql内置函数,基本概念,数据库和数据库管理系统 数据库是在计算机上组织、存储和共享数据的方法，数据库系统是由普通的文件系统发展而来的。数据库系统具有较高的数据独立性，即不依赖于特定的数据库应用程序；数据库系统的数据冗余小，可以节省数据的存储空间；另外数据库系统还很容易实现多个用户的数据共享。 数据库系统成熟的标志就是数据库管理系统的出现。数据库管理系统(database managerment system，简称dbms)是对数据库的一种完整和统一的管理和控制机制。数据库管理系统不仅让我们能够实现对数据的快速检索和维护，还为数据的安全性、完整性、并发控制和数据恢复提供了保证。数据库管理系统的核心是一个用来存储大量数据的数据库。,一个真正的数据库系统由硬件和软件两个方面构成。 比如我们要使用oracle数据库，需要安装oracle公司提供的数据库服务器软件和一台用于安装数据库管理系统的高性能的计算机服务器。 数据库系统的发展经历了层次模型、网状模型及关系模型几个阶段。当今应用最普遍的是关系型数据库管理系统。 目前，市场上流行的几种大型数据库，如oracle、db2、sybase、ms sql server等都是关系型数据库管理系统。oracle数据库是一种面向对象的关系型数据库管理系统(ordbms)，是基于标准sql语言的数据库产品。,基本概念,在数据库的设计阶段，需要创建逻辑模型。关系数据库的逻辑模型叫做实体关系模型。 实体模型化最常用的工具是实体关系图，简称er(entityrelationship)图，它是一种简单的图形技术，用来定义数据库中需要的表、字段和关系。它用于数据库设计的第一步，与我们使用的具体的数据库管理系统无关。实体关系模型的优点是： 有效地搜集和表示组织的信息需求。 提供一个容易理解的系统描述图。 易于开发和提炼。 明确定义了信息需求的范围。 将业务需求信息与业务执行活动分开。 根据业务说明或描述创建实体关系图。,实体关系模型,典型的实体关系模型有以下三个要素： 实体：客观存在并可以相互区分的事物称为实体，包括有意义的人、地方或事物，如学生、教师、课程、成绩等。 属性：实体所具有的某一特性称为属性，一个实体可以用若干属性来刻画，如学生实体具有学号、姓名、性别等属性。 关系：两个实体之间的相关性，如学生与课程之间的关系，教师与课程之间的关系。,实体关系模型三要素,实体关系,实体之间的关系有三种类型： 一对一：表示一个实体中的一种情况只与另一个实体中的一种情况有关系。比如：学生与学生证，一个学生只对应一个学生证，一个学生证只对应一个学生。 一对多：表示一个实体中的一种情况与另一个实体中的多种情况有关系。比如：班级与学生，一个班级可有多个学生，而一个学生只能属于某一个班级。 多对多：表示一个实体中的一种情况与另一个实体中的多种情况有关系，而第二个实体中的一种情况也与第一个实体中的多种情况有关系。比如：教师与学生，一个学生有多个教师为其上课，一个教师要为多个学生上课,er图,在er模型图中，用实线表示实体之间必须有关系，用虚线表示实体之间是可选的关系，用三角表示一对多关系。 在实体的属性中，在属性前用“*”表示必须有的属性，用“#”表示惟一属性，小写字母“o”代表可选属性。在每一实体上，要定义一个惟一表示该实体的标识符，称为uid(unique identifier)，uid是属性之间的组合。图1-3表示了三个实体之间关系的er图，其中系部id、专业id和教师id分别是三个实体的uid。,规范化,第一范式（1nf）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。 符合第一范式: 学号, 姓名, 年龄,地址 不符合第一范式: 学号, 姓名年龄,地址 .,第二范式（2nf）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。 学生选课表: ss(学号, 姓名, 年龄, 课程名称, 成绩, 学分) 不符合第二范式.,引起的问题: (1) 数据冗余： 同一门课程由n个学生选修，“学分”就重复n-1次；同一个学生选修了门课程，姓名和年龄就重复了n-1次。 (2) 更新异常： 若调整了某门课程的学分，数据表中所有行的“学分”值都要更新， 否则会出现同一门课程学分不同的情况。 (3) 插入异常： 假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有 “学号”关键字，课程名称和学分也无法记录入数据库。 (4) 删除异常： 假设一批学生已经完成课程的选修，这些选修记录就应该从数据库表中删除。但是，与此同时，课程名称和学分信息也被删除了。很显然，这也会导致插入异常。,把选课关系表ss改为如下三个表： 学生：sn(学号, 姓名, 年龄)； 课程：s(课程名称, 学分)； 选课关系：ss(学号, 课程名称, 成绩)。 这样的数据库表是符合第二范式的，消除了数据冗余、更新异常、插入异常和删除异常。 另外，所有单关键字的数据库表都符合第二范式，因为不可能存在组合关键字。,第三范式（3nf）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在“a “的决定关系，则传递函数依赖于a。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 非关键字段x 非关键字段y,学生关系表为sn(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)， 关键字为单一关键字“学号” . 这个数据库是符合2nf的，但是不符合3nf，因为存在如下决定关系： (学号) (所在学院) (学院地点,学院电话) 即存在非关键字段“学院地点”、“学院电话”对关键字段“学号”的传递函数依赖。 它也会存在数据冗余、更新异常、插入异常和删除异常的情况，读者可自行分析得知。 把学生关系表分为如下两个表： 学生：(学号, 姓名, 年龄, 所在学院)； 学院：(学院, 地点, 电话)。 这样的数据库表是符合第三范式的， 消除了数据冗余、更新异常、插入异常和删除异常。 注:满足第一范式,第二范式,第三范式的数据库结构不一定是最优的数据库设计.,目录,数据库基本概念 数据查询 基本语法 排序查询 条件查询 高级查询 数据操作 表和视图 sql内置函数,数据库查询语言sql,主要特点： sql语言可以在oracle数据库中创建、存储、更新、检索和维护数据，其中主要的功能是实现数据的查询和数据的插入、删除、修改等操作。 sql语言在书写上类似于英文，简洁清晰，易于理解。它由关键字、表名、字段名，表达式等部分构成。 分类： sql语言按功能可分为ddl语言、dml语言、dcl语言和数据库事务处理语言四个类别。 sql语言的主要关键字有：alter、drop、revoke、audit、grant、rollback、commit、insert、select、comment、lock、update、create、noaudit、validate、delete、rename等。,sql语言的分类,按照sql语言的不同功用，可以进一步对sql语言进行划分。下表给出了sql语言 的分类和功能简介。,由主句和若干个从句组成，主句和从句都由关键字引导。 主句表示该语句的主要功能 从句表示一些条件或限定，有些从句是可以省略的。 在语句中会引用到列名、表名或表达式。另外还有如下一些说明： 关键字、字段名、表名等之间都要用空格或逗号等进行必要的分隔。 语句的大小写不敏感(查询的内容除外)。 语句可以写在一行或多行。 语句中的关键字不能略写和分开写在两行。 要在每条sql语句的结束处添加“;”号。 为了提高可读性，可以使用缩进。 从句一般写在另一行的开始处。,sql基本语法,查询语句是最常见的sql语句，它从给定的表中，把满足条件的内容检索出来。以下是最基本的select语句语法。 select (字段名列表|*) from 表名 where 条件; select为查询语句的关键字，该关键字不能省略。 字段名列表代表要查询的字段。 from 也是查询语句关键字，后面跟要查询的表名，该关键字不能省略。 where条件限定检索特定的记录，满足“条件”的记录被显示出来，不满足条件的被过滤掉。 语句查询的结果往往是表的一部分行和列。如果字段名列表使用*，将检索全部的字段。如果省略where条件，将检索全部的记录。 select * from emp where deptno=10 ;,sql查询语句,基本查询语句,1基本查询 select * from dept ; 2显示行号 rownum 每个表都有一个虚列rownum，它用来显示结果中记录的行号。我们在查询中也可以显示这个列。select rownum,ename from emp; select * from(select rownum no ,id,name from student) where no15 ; select * from (select rownum no ,id,name from student where rownum=2; 3显示计算列 在查询语句中可以有算术表达式，它将形成一个新列，用于显示计算的结果，通常称为计算列。表达式中可以包含列名、算术运算符和括号。括号用来改变运算的优先次序。常用的算术运算符包括： +：加法运算符。 ?：减法运算符。 *：乘法运算符。 /：除法运算符。,基本查询语句,4 连接运算符 在前面，我们使用到了包含数值运算的计算列，显示结果也是数值型的。我们也可以使用字符型的计算列，方法是在查询中使用连接运算。连接运算符是双竖线“|”。通过连接运算可以将两个字符串连接在一起。 5 使用别名 as 我们可以为表的列起一个别名，它的好处是，可以改变表头的显示。特别是对于计算列，可以为它起一个简单的列别名以代替计算表达式在表头的显示。 说明：表头显示的是列别名，转换为汉字显示。在列名和别名之间要用as分隔，如ename和它的别名“名称”之间用as隔开。as也可以省略，如sal和它的别名“工资”之间用空格分割。 注意：如果用空格分割，要区别好列名和别名，前面为列名，后面是别名。 别名如果含有空格或特殊字符或大小写敏感，需要使用双引号将它引起来。,6消除重复行 如果在显示结果中存在重复行，可以使用的关键字distinct消除重复显示。 select distinct job from emp; 7. 查询结果的排序 select 字段列表 from 表名 where 条件order by 字段名1 asc|desc,字段名 asc|desc.; asc 升序（默认）|desc 降序 select ename, sal from emp order by sal; 可以按多列进行排序，先按第一列，然后按第二列 如果要对计算列排序，可以为计算列指定别名，然后按别名排序。,基本查询语句,条件查询,简单条件查询 要对显示的行进行限定，可在from从句后使用where从句，在where从句中给出限定的条件，因为限定条件是一个表达式，所以称为条件表达式。条件表达式中可以包含比较运算，表达式的值为真的记录将被显示。 例子：,条件查询,复合条件查询 可以用逻辑运算符构成复合的条件查询，即把两个或多个条件，用逻辑运算符连接成一个条件。有3个逻辑运算符，如下表所示。 运算的优先顺序是not，and，or。如果要改变优先顺序，可以使用括号。,条件查询条件特殊表示法,特殊条件查询,betweenand in is not null like 使用like操作符可完成按通配符查找字符串的查询操作，该操作符适合于对数据进行模糊查询。其语句法为： not like 匹配模式 匹配模式中除了可以包含固定的字符之外，还可以包含以下的通配符： %：代表0个或多个任意字符。 _ ：代表一个任意字符。,函数数值型函数,函数字符型函数,1. trim在一个字符串中去除另一个字符串: select trim(leading | trailing|both s from sstsrings) from dual ; leading代表去除左边的,trailing代表去除右边的,both 代表去除两边的. 2.replace :用一个字符串替换另一个字符串中的子字符串. replace (abc,b,c),函数日期型函数,函数类型转换函数,1自动类型转换 oracle可以自动根据具体情况进行如下的转换： * 字符串到数值。 * 字符串到日期。 * 数值到字符串。 * 日期到字符串。,2日期类型转换 将日期型转换成字符串时，可以按新的格式显示。 如格式yyyy-mm-dd hh24:mi:ss表示“年-月-日 小时:分钟:秒”。oracle的日期类型是包含时间在内的。,日期类型转换,日期类型转换2,数值转换符,select to_number( +123,s99999) from dual,其他常用函数,目录,数据库基本概念 数据查询 基本语法 排序查询 条件查询 高级查询 数据操作 表和视图 sql内置函数,高级查询,多表联合查询 通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在where子句中用比较运算符指明连接的条件。 忘记说明表的连接条件是常见的一种错误，这时查询将会产生表连接的笛卡尔积(即一个表中的每条记录与另一个表中的每条记录作连接产生的结果)。一般n个表进行连接，需要至少n-1个连接条件，才能够正确连接。两个表连接是最常见的情况，只需要说明一个连接条件。 相等连接。 不等连接。 外连接。 自连接。,相等连接 通过两个表具有相同意义的列，可以建立相等连接条件。使用相等连接进行两个表的查询时，只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。 外连接 相等连接有一个问题：如果某个值内容在另一张表中没有对应的记录存在，那么在查询中就不会出现相应的结果； 为了解决这个问题可以用外连，即除了显示满足相等连接条件的记录外，还显示那些不满足连接条件的行，不满足连接条件的行将显示在最后。 不等连接 自连接 自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别两个相同的表。,联合查询,统计查询,通常需要对数据进行统计，汇总出数据库的统计信息。这个功能可以由统计查询完成。 oracle提供了一些函数来完成统计工作，这些函数称为组函数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于select子句、having子句或order by子句中。组函数也可以称为统计函数。,分组函数中sum和avg只应用于数值型的列，max、min和count可以应用于字符、数值和日期类型的列。组函数忽略列的空值。 使用group by 从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。 如果不使用分组，将对整个表或满足条件的记录应用组函数。 在组函数中可使用distinct或all关键字。all表示对所有非null值(可重复)进行运算(count除外)。distinct 表示对每一个非null值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为all。 对分组查询的结果进行过滤，要使用having从句。having从句过滤分组后的结果，它只能出现在group by从句之后，而where从句要出现在group by从句之前。,统计查询,我们可能会提出这样的问题，在雇员中谁的工资比scott高？ 第一步查询雇员scott的工资 第二步查询工资高于scott的雇员。 通过把一个查询的结果作为另一个查询的一部分，可以实现这样的查询功能。 第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中，这就是子查询。出现在其他查询中的查询称为子查询，包含其他查询的查询称为主查询。 子查询一般出现在select语句的where子句中，oracle也支持在from或having子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在select、insert、update、delete等语句中使用。,子查询,题目讲解: 1.有如下两张表: a 部门表 dept (deptid 部门编号, dname 部门名称) b 员工表 emp (emno员工编号, ename 员工名称,deptid 员工所在的部门编号 ) 问题:用一条sql语句查询每个部门共有多少人. 结果如下: dname employeenum 人力资源部 5 财务部 2 销售部 4,2.有如下三张表: 用户表:scmuser(用户帐号 密码 用户姓名 创建时间) account password name createdate 001 123456 zhang 2008-05-12 002 123456 lisi 2008-04-12 003 123456 wang 2008-12-12 系统权限表:systemmodel ( 模块编号 模块名称) modelcode modelname 1 采购 2 销售 3 库存 4 收支 5 报表,用户权限表:usermodel(用户编号 模块编号 ) account modelcode 001 1 001 2 002 2 002 3 003 1 003 2 003 5 要求: 用一句sql找出创建时间(createdate)在lisi之后的所有用户的用户名(name)和其对应的权限名称(modelname) select ,sm.modelname from scmuser su ,usermodel um ,systemmodel sm where su.account =um.account and um.modelcode= sm.modelcode and su.createdate all (select createdate from scmuser where name=lisi) 结果如下: name modelname zhang 采购 zhang 销售 wang 采购 wang 销售 ,3.有以下两张表: 班级表class:classid（number(4,0)）,classname,description 学生表stu:stuid（number(4,0)）,stuname,sex,classid(外键) 1.写sql语句，找到学生个数大于3个的班级id、该班级所有学生id、学生名称、班级学生个数 2.写sql语句，找到学生个数大于3个的班级id、班级名称、该班级所有学生id、学生名称、班级学生数,集合运算,多个查询语句的结果可以做集合运算，结果集的字段类型、数量和顺序应该一样。,目录,数据库基本概念 数据查询 数据操作 表和视图 sql内置函数,数据库操作语句dml,插入数据 可以使用insert命令，向已经存在的表插入数据，语法格式如下： insert into 表名 (字段列表) values(表达式1, 表达式2,.)|query语句； 修改数据 修改数据的语句update对表中指定字段的数据进行修改，一般需要通过添加where条件来限定要进行修改的行，如果不添加where条件，将对所有的行进行修改。 (1)修改数据的语句update的基本语法如下：update 表名 set 字段名1=表达式1, 字段名2=表达式2, . where 条件; (2)update语句的另外一种用法：update 表名 set(字段名1, 字段名2, .)=select (字段名1, 字段名2, .) from 另外的表名where条件; 删除数据 delete from表名 where 条件;,数据库操作语句,目录,数据库基本概念 数据查询 数据操作 表和视图 表的创建和操作 数据完整和约束条件 sql内置函数,oracle数据库对象,create table 表名(列名 数据类型(宽度)default 表达式column constraint,. table constraint table_partition_clause ); 创建表最主要的是要说明表名、列名、列的数据类型和宽度，多列之间用“,”分隔。 可以是用中文或英文作为表名和列名。 表名最大长度为30个字符。在同一个用户下，表不能重名，但不同用户表的名称可以相重。另外，表的名称不能使用oracle的保留字。在一张表中最多可以包含2000列。该语法中的其他部分根据需要添加，作用如下： default 表达式：用来定义列的默认值。 column constraint：用来定义列级的约束条件。 table constraint：用来定义表级的约束条件。 table_partition_clause：定义表的分区子句。,表的创建,例子: create table test ( id number(6,2) not null , name varchar2(20) default mao not null, age number, deptno number not null, comm varchar2(50) not null, primary key(id), foreign key (deptno) references dept (deptno), check (age0 and age150) );,通过子查询创建表 如果要创建一个同已有的表结构相同或部分相同的表，可以采用以下的语法： create table 表名(列名.) as sql查询语句; 该语法既可以复制表的结构，也可以复制表的内容，并可以为新表命名新的列名。新的列名在表名后的括号中给出，如果省略将采用原来表的列名。复制的内容由查询语句的where条件决定。 删除已创建的表 drop table 表名cascade constraints; 表的删除者必须是表的创建者或具有drop any table权限。cascade constraints表示当要删除的表被其他表参照时，删除参照此表的约束条件. 注意: 1.如果表中没有字段被别的表作为外键,那么直接 drop table表名 即可, 2.如果表中有字段被别的表作为外键,那么需要使用drop table 表名 cascade constraints,即删除表,并解除与此表相关的外键关系(不管别的表设置的外键是什么类型的外键,与此表相关的外键关系都会被解除),表的创建和删除,表的重命名 rename 旧表名 to 新表名; 清空表 truncate table 表名；ddl 清空表可删除表的全部数据并释放占用的存储空间。有关训练请参照delete(dml)语句部分，注意两者的区别。 添加注释 (1) 为表添加注释：comment on table 表名 is .; (2) 为列添加注释：comment on column 表名.列名 is . 查看表 可以通过对数据字典user_objects的查询，显示当前模式用户的所有表。select object_name from user_objects where object_type=table;,表的操作,目录,数据库基本概念 数据查询 数据操作 表和视图 表的创建和操作 数据完整和约束条件 视图的创建和操作 sql内置函数,数据完整性和约束条件,表的数据有一定的取值范围和联系，多表之间的数据有时也有一定的参照关系。在创建表和修改表时，可通过定义约束条件来保证数据的完整性和一致性。约束条件是一些规则，在对数据进行插入、删除和修改时要对这些规则进行验证，从而起到约束作用。 完整性包括数据完整性和参照完整性 数据完整性定义表数据的约束条件: 主键(primary key) 非空(not null) 惟一(unique) 检查(check)约束条件定义. 参照完整性定义数据之间的约束条件。参照完整性由外键(foreign key)约束条件定义。,外键约束,第一种语法，如果子记录存在，则不允许删除主记录： constrant 约束名 foreign key(列名1,列名2,.)references 表名(列名1,列名2,.) 第二种语法，如果子记录存在，则删除主记录时，级联删除子记录： constrant 约束名 foreign key(列名1,列名2,.)references 表名(列名1,列名2,.)on delete cascade 第三种语法，如果子记录存在，则删除主记录时，将子记录置成空： constrant 约束名 foreign key(列名1,列名2,.)references 表名(列名1,列名2,.)on delete set null其中的表名为要参照的表名。,有外键约束时删除表记录的情况: 班级表class(classid,cname) 学生表stu(stuid,sname,classid) 1.学生表里的classid设置的是no action类型的外键约束(即上一页的第一种语法定义).那么当执行delete from class 时,就会报错,不允许删除主记录. 2.学生表里的classid设置的是cascade类型的外键约束(即上一页的第二种语法定义).那么当执行delete from class 时,就会连同子记录整条记录一起删除.即删除两条记录,主表一条,子表一条. 3.学生表里的classid设置的是 set null类型的外键约束(即上一页的第三种语法定义).那么当执行delete from class 时,就会把学生表stu中相关的记录的classid字段设置成null.,数据字典user_constraints中包含了当前模式用户的约束条件信息。其中，constraints_type 显示的约束类型为： c：check约束。 p：primary key约束。 u：unique约束。 r：foreign key约束。 其他信息可根据需要进行查询显示，可用describe命令查看user_constraints的结构。 select constraint_name,constraint_type,search_condition from user_constraints where table_name=图书; 使约束条件失效：alter table 表名 disable constrant 约束名; 使约束条件生效：alter table 表名 enable constrant 约束名;,查看约束条件,目录,数据库基本概念 数据查询 数据操作 表和视图 表的创建和操作 数据完整和约束条件 视图的创建和操作 sql内置函数,视图的概念,视图是基于一张表或多张表或另外一个视图的逻辑表。 视图不同于表，视图本身不包含任何数据。表是实际独立存在的实体，是用于存储数据的基本结构。而视图只是一种定义，对应一个查询语句。视图的数据都来自于某些表，这些表被称为基表。通过