Oracle数据库及SQL基础.pptVIP

欢迎参加Oracle数据库培训班 主要内容数据库及SQL基础Oracle8对象关系数据库DBA数据库系统管理Oracle的开发工具OAS应用开发 一 Oracle数据库及SQL基础 主要内容 数据库概述及SQL基础SQL Plus环境及报表功能PL SQL程序设计存储过程 触发器 包PL SQL编程接口 实用工具包分布式数据库并发控制 主要内容 对象关系数据库表和索引的分区SQL语句的并行大对象字段综合例题 计算机数据管理的演变 人工管理阶段没有支持数据管理的软件在程序中要规定数据的逻辑结构和物理结构 数据与程序不独立 数据组织面向应用 数据不能共享 数据重复 数据处理 批处理 程序1 程序2 数据2 数据1 计算机数据管理的演变 文件系统阶段信息以文件为单位存储在外存 且由OS统一管理 逻辑结构与物理结构脱钩 使数据与程序有了一定的独立性 实现了以文件为单位的共享 数据组织 逻辑结构 仍面向应用 所以不同应用的数据存在冗余 文件之间相互独立 不能反映现实世界中事物之间的联系 数据处理 分时 批处理 计算机数据管理的演变 文件系统阶段 应用程序3 应用程序2 应用程序1 文件1 文件2 存取方法 OS 计算机数据管理的演变 数据库系统阶段面向企业或部门以数据为中心组织数据 形成综合性的数据库 为各应用共享 数据冗余小 节省存储空间 有利于保持数据的一致性程序和数据有较高的独立性 数据易修改 易扩充 具有良好的用户接口 用户可方便地开发和使用数据库 提供了数据的安全性 完整性 以及并发控制 计算机数据管理的演变 数据库系统阶段 应用程序3 应用程序2 应用程序1 DBMS 数据库系统结构 在DB中 对于同一意义下的数据 如学生记录 从计算机中处理的二进制表示到用户处理的诸如学生姓名 学号等概念的数据之间 存在着许多层次的抽象和转换 DBMS支持把DB从逻辑上分为三个层次 三级模式 它是数据库系统的一个总体框架 是对数据库从不同角度和层次上的描述 内模式 模式 外模式3 外模式2 外模式1 应用A 应用E 应用B 应用C 应用D DBMS 外模式 模式映象 模式 内模式映象 数据库系统的三级模式 数据字典 数据库管理系统 操作系统 数据库 系统缓冲区 DB工作流程 数据模型 数据模型是模型化数据和信息的工具 现实世界 认识抽象 信息世界 概念模型 机器世界 DBMS支持的数据模型 转换 E R图 学号 姓名 性别 学分 课程名 课程号 成绩 E R图 供应 库存 领导 工作 供应商 仓库 职工 零件 项目 供应量 库存量 项目 项目号 开工日期 预算 E R图转换成关系模式 1 1 系 系号 系名 电话 系主任 系主任 工作证号 姓名 年龄 E R图转换成关系模式 1 N 学生 学号 姓名 性别 系号 系 系号 系名 电话 系主任 E R图转换成关系模式 M N 学生 学号 姓名 性别 系号 课程 课号 课名 学分 选课 学号 课号 成绩 成绩 实体集 关系联系 关系 由联系的各实体集主码属性 以及联系本身的属性组成 E R图转换成关系模式 职工 职工号 姓名 年龄 领导 供应 供应商 项目 零件 数量 Oracle关系数据库概述 子程序调用接口OCI Develop2000 Pro C预编译接口 SQL Plus SQL Forms Menu 操作系统 RDBMSORACLE核心 DB 终端 DB DB 网络 虚终端 三级模式 Oracle数据存储空间 基本表 Table View 视图 SQL Table Table 表与视图 基本表本身是实际且独立存在的 是数据库存储数据的基本单元 视图视图是虚表数据库只存储视图的定义 不存储对应的数据 视图用一组查询来定义 是被存储的查询 SQL语言基础 Select Fromemp 六种类型的SQL语句 数据定义语句DDL数据操纵语句DML事务控制语句会话控制语句系统控制语句嵌入式SQL 表格实例之一 选课 学号 课程号 成绩 sc sno cno grade 课程 课程号 课程名 学分 course cno cname credit 系 系号 系名 电话 dep dno dname tel 学生 学号 姓名 性别 出生年月 系号 student sno sname sex birthday dno 表格实例之二 emp eno ename age sal mgr dno 职工职工号姓名年龄工资领导部门号其中 mge为职工的上一级领导 即上一级领导的职工号 CREATETABLEstudent snoNUMBER 6 snameCHAR 6 sexCHAR 2 birthdayDATE dnoCHAR 3 建立表对象 列数据类型 字符型 CHAR n 字符数据 长度为n个字符VARCHAR2 n 变长字符型 n为最大长度 数值型 NUMBER n 数值数据 最大可达105位NUMBER m n m位数字 其中n为小数位日期型 DATE 其格式为DD MON YY例如 17 DEC 85或17 12月 85 模式 Createorreplace模式名 表名模式逻辑地组织数据库对象不同模式中的对象可以有相同的名字在Oracle系统中 模式名与用户名是一一对应的scott用户拥有emp表scott模式含有emp表sys模式中含有数据字典 系统表 系统目录 完整性约束 1 主码完整性约束snonumber 6 constrainte1primarykey 2 非空完整性约束snamechar 6 constrainte2notnull 3 唯一完整性约束dnamechar 20 constraintd2unique 完整性约束 4 check完整性约束agenumberconstrainte3check agebetween18and60 sexchar 2 check sexin 男 女 5 引用完整性约束dnochar 3 constrainte4referencesdep dno ondeletecascade 引用完整性约束 先建主表 后建子表子表引用的必须是主表的主码 或定义了unique约束的列 子表dno的值必须是合法值 主表值 当删除主表记录时 若子表中有对应记录 不能删 指定了ondeletecascade连带删除 CREATETABLEsc snonumber 6 cnochar 3 gradenumber 3 primarykey sno cno CREATETABLE notnull 列1 列2 unique 列1 列2 表约束 CREATETABLEstudent foreignkey dno referencesdep dno CREATETABLEemp enochar 3 enamechar 6 sexchar 2 agenumber 2 dnochar 3 CHECK sex 男 ANDageBETWEEN18AND60ORsex 女 ANDageBETWEEN18AND55 表约束 完整性约束的作用时间 createtablestudent enableconstrainte1enableconstrainte2 disable 表对象的修改与删除 ALTERTABLEempADD 列定义 MODIFY enamechar 10 ADD 表约束 DROPCONSTRAINTe4 DROPTABLEemp DML语句 DB写语句 INSERTINTOempVALUES e01 李宏 32 UPDATEempSETsal sal 1 1WHEREdno d01 DELETEFROMempWHEREdno d01 数据库查询 1 基本查询 SELECT FROMstudentWHEREsex 男 ANDdno D01 查询的结果仍可看成是一个关系 它由查出的记录组成 2 查询语句中的投影操作 投影操作是从关系的属性集中选择属性子集 即由关系的部分列组成一个新关系 SELECTsno sname dnoFROMstudent 2 查询语句中的投影操作 SELECTsnostu no snamestu name dnodeptFROMstudentWHEREsex 男 该语句输出如下 stu nostu namedept980001张自立d01970020李春生d02 2 查询语句中的投影操作 SELECTename sal 12an salFROMemp 该语句输出如下 enamean sal徐再兴9600 SELECTename sal 12an sal 元 RMBFROMemp 该语句输出如下 enamean salRMB徐再兴9600元 3 查询语句中的选择操作 WHERE子句 选择操作是从关系中选择部分满足条件的元组 其表示形式为 SELECT FROMstudentWHEREdno d01 3 查询语句中的选择操作 WHERE子句 例 查询79 80两年出生的女同学的名单 SELECTsname birthdayFROMstudentWHEREsex 女 ANDbirthdayBETWEEN 01 1月 79 AND 31 12月 80 查询1980年以前出生的学生 SELECT FROMstudentWHEREbirthday 01 1月 1980 SQL语句中的运算符 算术运算符 逻辑运算符 与AND 或OR 非NOT比较符 其他运算符 IN NOTIN ANY ALL判断是否是集合的成员 BETWEEN AND 判断列值是否满足指定的区间LIKE匹配模式 IS NOT NULL测试空值 3 查询语句中的选择操作 WHERE子句 在查询条件中 可以使用集合运算符 例如 IN SELECTdname telFROMdepWHEREdnameIN 计算机系 外语系 中文系 该表达式等同于 dname 计算机系 ORdname 外语系 ORdname 中文系 该表达式也等同于 dname ANY 计算机系 外语系 中文系 集合运算符NOTIN是IN的反义词 WHEREdnameNOTIN 计算机系 外语系 中文系 该表达式等同于 dname ALL 计算机系 外语系 中文系 3 查询语句中的选择操作 WHERE子句 空值是表中分量常见的一种状态查询学号为980001的同学正在学习的课程 SELECTcnoFROMscWHEREsno 980001ANDgradeISNULL 模糊匹配 匹配符有两个 表示任意一个字符 表示任意个字符 包括0个 例如 查询姓王的同学信息 SELECT FROMstudentWHEREsnameLIKE 王 4 查询的排序输出 ORDERBY子句 查询c01课程的成绩 并按成绩由高到低的顺序输出 SELECTsno grade 输出学号和成绩 FROMscWHEREcno c01 andgradeISNOTNULLORDERBYgradeDESC 排序输出的隐含顺序是升序 ASC 如果要求按列值的降序输出 需在列名后指定DESC ORDERBY子句指定的排序列可以不止一个 例如 SELECT FROMstudentORDERBYdepDESC birthday 4 查询的排序输出 ORDERBY子句 ORDERBY子句除了用列名指定排序列外 还可以依据SELECT子句中列的顺序号排序 这对于某些列名不好指定的情况尤为有用 例如 查询职工姓名及年工资 并按年工资的升序依次输出 SELECTename sal 12FROMempORDERBY2 排序应该是查询语句的最后一步工作 是即将输出之前的处理 所以排序子句一般也出现在查询语句的最后 5 多表查询与连接操作 joins 笛卡尔积操作将结果关系扩展 如果笛卡尔积的两关系元组数分别为m和n 那么结果关系的元组数为m n 而在处理中往往只是需要其中部分元组 连接 Join 操作是从笛卡尔积中选择满足条件的元组 最常用的条件是等值连接 Select fromstudent depwherestudent dno dep dno dno student dep 5 多表查询与连接操作 joins 表之间的连接操作 通常是自然连接 体现在WHERE子句中的连接条件 例 查询计算机系的学生信息SELECTsno sname sexFROMstudent depWHEREstudent dno dep dnoANDdname 计算机系 5 多表查询与连接操作 joins 查询物理课不及格的学生名单 输出学生的学号 姓名及成绩 SELECTstudent sno sname gradeFROMstudent sc courseWHEREcname 物理 ANDgrade 60ANDo oANDsc sno student sno 在该例中 查询的结果来自两张表 而决定查询结果的因素涉及到三张表的信息 Selectsname dnamefromstudent depwherestudent dno dep dnoanddep dnoin d01 d02 d03 Enamedname wangdep1zhangdep1lidep2dep3 多表查询 外查询 显示目前没有学生的系 selectdnamefromstudent depwherestudent dno dep dnoandsnoisnull 多表查询 外查询 6 元组变量 在一个查询过程中有时会涉及到同一关系的两个 或几个 元组 为了表示不同的元组 SQL允许在FROM子句中为一个关系定义几个元组变量 这样 我们就能在SELECT WHERE等子句中 通过在属性前冠以元组变量来引用同一关系不同元组的属性值 例如在emp表中 我们要查询职工王莹的上级领导信息SELECTeno enameFROMempWHEREename 王莹 ANDmgr eno 为emp表定义两个元组变量 e1 e2 SELECTe2 eno e2 enameFROMempe1 empe2WHEREe1 ename 王莹 ANDe1 mgr e2 eno 6 元组变量 athlete运动员 name姓名 sex性别 group组 查询混双选手名单 每行输出一对选手 男在前 女在后 SELECTa1 nameman a2 namewomanFROMathletea1 athletea2WHEREa1 sex 男 ANDa2 sex 女 ANDa1 group a2 group 该例检索结果如下 manwoman王涛邓亚萍 6 元组变量 交 差 并 7 集合运算 7 集合运算 selectsnofromscwherecno c01 selectsnofromscwherecno c02 交 union 并 minus 差 intersect 查同时选修了c01和c02两门课的学生SELECTsnoFROMscwherecno c01 andcno c02 7 集合运算 例 查询选修了c01 但没有选修c02的学生的学号 SELECTsnoFROMscWHEREcno c01 MINUS SELECTsnoFROMscWHEREcno c02 7 集合运算 例 假如外语只开设了英语和日语 查询当前至少上着一门外语课的学生 SELECTsnoFROMsc courseWHEREcname 英语 ANDo oANDgradeISnull UNION SELECTsnoFROMsc courseWHEREcname 日语 ANDo oANDgradeISnull 7 集合运算 Select语句必须选择同样数量的列 相应的列必须具有相同的类型 重复的行将被自动去掉 不用distinct第一个select语句中的列名出现在结果Orderby子句可出现在语句的最后 且只能指定位置列 Selectempno empname salaryfromworker union selecteno ename salfromemp orderby3 8 查询中的重值处理 我们的讨论中存在这样一个事实 即SQL把关系当作一个 袋子 而不是一个集合 也就是说 一个元组在关系中可以出现若干次 例如 我们要查询一下所有的课程名 SELECTcnameFROMcourse 只需在关键字SELECT的后面加上关键字DISTINCT即可 它告诉SQL在结果关系中只产生元组的一份拷贝 因此可以保证结果中没有重复值 例如 上面提到的查询可以写成这样 SELECTDISTINCTcnameFROMcourse 8 查询中的重值处理 在前面讨论关系的集合运算 结果关系的重复值将会自动去掉 但如果我们在集合运算符 如UNION 的后面加上关键字ALL 那结果关系又会具有 袋子 的效果 即不去掉重复值 例如 查询至少选修了c01和c02中一门课的学生 SELECTsnoFROMscWHEREcno c01 UNIONALL SELECTsnoFROMscWHEREcno c02 8 查询中的重值处理 DISTINCT的使用是要付出代价的 因为要去掉重复值 必须对结果关系进行排序 相同的元组排在一起 只有按这种方法对元组分组 才能去掉重复值 而这一工作甚至比查询本身还费时间 聚合是把关系作为运算对象的另一类操作 这里所说的聚合操作就是对关系中一列的值进行统计工作 最终得到一个统计值 例如求职工的平均年龄 求职工的工资总额等 常用5种聚合的操作符 SUM 对某列的值求和 AVG 求某列值的平均值 MIN 求某列值的最小值 MAX 求某列值的最大值 COUNT 统计某列值的个数 计数 9 聚合操作 9 聚合操作 例 统计职工的工资总额 SELECTSUM sal FROMemp 例 求职工的最高工资 最低工资和平均工资 SELECTMAX sal MIN sal AVG sal FROMemp 例 求女学生的学生总数 SELECTCOUNT FROMstudentWHEREsex 女 9 聚合操作 查询有多少名同学物理课不及格 SELECTCOUNT FROMsc courseWHEREcname 物理 ANDo oANDgrade 60 将关系的元组按一定条件分组 然后对每一个组进行统计 求每个系的学生数而不是求学生总数 希望得到下面的输出 dnocount d01120d0265 SELECTdno count FROMstudentGROUPBYdno d01 d01 d01 d01 d01 d01 23 d01 45 100 76 221 65 90 sum max dno count 9 聚合操作 分组统计 9 聚合操作 分组统计 查询每个学生已获得的学分 注 成绩及格即取得相应课程的学分 SELECTsno sum credit FROMsc courseWHEREgrade 60ANDo oGROUPBYsno 分组子句中可以指定多个用于分组的属性 当按第一个属性值分组后 每组再按第二个属性值分组 统计是对最小的组进行 查询各系c01课程最高成绩和最低成绩 男生 女生分开统计 SELECTdno sex MAX grade MIN grade FROMstudent scWHEREstudent sno sc snoANDcno C01 GROUPBYdno sex dnosexMAX grade MIN grade d01男10045d01女9861d02男9460d02女9057 9 聚合操作 分组统计 9 基于聚合操作结果的选择 当我们的选择是基于组的统计结果时 要在与GROUPBY子句配合使用的HAVING子句中写上这类选择组的条件 例如 查学分大于30分的学生 输出学号及学分 SELECTsno sum credit FROMsc courseWHEREgrade 60ANDo oGROUPBYsnoHAVINGsum credit 30 10 子查询 所谓子查询就是一个用括号括起的表达式 它对关系求值 例如 一个SELECT FROM WHERE表达式就可以是一个子查询 注意 子查询的嵌套如何处理子查询的返回值相关子查询和非相关子查询 一个子查询可以产生一个单纯数据 如果是这种情况 该子查询就如同一个常量 或是一个表示元组分量的属性 那么我们就可以象使用常量或属性一样来使用子查询 例如 查询物理系的学生名单 按以前的做法是 SELECTsnameFROMstudent depWHEREdname 物理系 ANDdep dno student dno 而利用子查询的语句如下 SELECTsnameFROMstudentWHEREdno SELECTdnoFROMdepWHEREdname 物理系 10 子查询 10 子查询 一个子查询也可以产生一个关系 该关系可以包含若干属性 可包含若干元组 SQL提供了若干对于关系的操作符 并产生一个布尔型的结果 这些操作符主要就用在子查询的结果上 EXISTS运算符EXISTSR是一个条件 当且仅当关系R不空时为真 集合运算符IN NOTIN ANY ALL 10 子查询 例 查体育课不及格的男生名单 SELECTsnameFROMstudentWHEREsex 男 ANDsnoIN SELECTsnoFROMscWHEREgrade 60ANDcnoIN SELECTcnoFROMcourseWHEREcname 体育 查找年龄在50岁以上 工资小于本部门平均工资的职工 输出他们的姓名 年龄 工资 SELECTename age salFROMempWHEREage 50andsal SELECTavg sal FROMempWHEREdno e1 dno e1 dno emp dno 10 子查询 相关查询 属性域 例 查询从未被选修的课程 相关子查询SELECTcno cnameFROMcourseWHERENOTEXISTS SELECT FROMscWHEREcno o 例 查询从未被选修的课程 另一种方法 非相关子查询SELECTcno cnameFROMcourseWHEREcnoNOTIN SELECTcnoFROMsc 10 子查询 10 子查询与聚合操作 例 查询本学期选课超过10门的学生人数 注 选修但还没有成绩就表示是本学期正在上的课 SELECTCOUNT FROMstudentWHEREsnoIN SELECTsnoFROMscWHEREgradeISNULLGROUPBYsnoHAVINGCOUNT 10 10 子查询与聚合操作 例 查询各系年龄最小的同学名单 SELECTsname birthday dnoFROMstudentWHERE birthday dno IN SELECTMAX birthday dnoFROMstudentGROUPBYdno 11 树查询 查询职工上下级关系selecteno enamefromempconnectbyprioreno mgrstartwitheno e01 selectlevel eno enamee ENOENAME e01aaae02bbbe03aaae04aaae05aaae06aaae07aaae08aaa LEVELENOENAME 1e01aaa2e02bbb3e03aaa3e04aaa2e05aaa3e06aaa3e07aaa3e08aaa 查同时选修c01和c02两门课的学生 selecta snofromsca scbwherea sno o c01 o c02 Selectsnofromscwherecno c01 andsnoin selectsnofromscwherecno c02 查询的不同实现 子查询的应用 createtableemp1asselecteno ename sal age dnofromemp insertintoemp1select fromempwheresex 男 建视图 createviewemp1Asselect fromempwheredno d01 orderbyage 修改视图 CREATEVIEWemp01SELECTeno ename age salFROMempWHEREdno d01 如果在视图emp01中插入该部门的一个新职工记录 插入语句如下 INSERTINTOemp01VALUES e01 李为 23 800 可修改视图CREATEVIEWemp01SELECTeno ename age sal dnoFROMempWHEREdno d01 修改视图 删除 DELETEFROMemp01WHEREage 60 该语句被转换为如下等价语句DELETEFROMempWHEREage 60ANDdno d01 修改 UPDATEemp01SETsal sal 1 1 该语句被转换为如下等价语句 UPDATEempSETsal sal 1 1WHEREdno d01 SQL语句中的函数 字符函数 例 selectdno lower dname fromdep 例 select fromempwhereename upper rose SQL语句中的函数 数值函数 例 selectenameround sal 2 round 782 934 2 782 93round 782 934 783round 782 934 1 780logsincosabssqrtfloor 99 9 99小于 等于参数值的最大整数 SQL语句中的函数 日期函数 例 selectsysdatefromsys dual dual dummy 日期的算术运算date numberdate numberdate dateselectd1 d1 7 d1 7 sysdate d1fromemp 04 jun 8411 jun 8428 may 841827 731 SQL语句中的函数 日期函数 例 selectmonths between sysdate hiredate fromempwheremonths between sysdate hiredate 24 65 087226 8292例 selectadd month hiredate 3 from 13 jun 9813 sep 9805 dec 9705 mar 98 SQL语句中的函数 日期函数 例 selectnext day sysdate Monday fromdual31 org 987 sep 98例 selectlast day sysdate fromdual 包含参数的月份中的最后一天 SQL语句中的函数 转换函数 to char sysdate 描述串 将隐含格式dd mon yy转换为指定格式格式串 yy mm dd98 06 11hh mi ss09 34 28dayMonday Dymon tuewed yyyy1998to char sal 9 999 1 375selectto char 3456 999 9 fromdual SQL Plus Sql plus环境报表功能 中文配置 regedit SIMPLIFIEDCHINESE CHINA ZHS16CGB231280 主机字符串配置 主机字符串配置 主机字符串配置 主机字符串配置 主机字符串配置 主机字符串配置 主机字符串配置 主机字符串配置 登录SQL Plus SQL Plus窗口 交互界面 Login sql初始化文件 SQL Plus命令 SQL select fromcat SQL descstudentSQL select fromstudent SQL Plus命令 SQL editaSQL a SQL Plus菜单使用 SQL Plus菜单使用 替换变量 insertintoempvalues entervalueforeno entervalueforename 98 12 31 第一页 年度财务报表 内部资料 请勿外传 列标题设计 Columnsalheading工资format 9999 99报表头尾标题设计 COLnTtitleSKIPnBtitleLEFTCENTERRIGHT行分组 BREAKON列名SKIP n page ON BREAKONdnoSKIP3分组计算 computesumo