Oracle数据库及SQL基础.ppt

欢迎参加 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支持的数据模型,转换,ER图,学号,姓名,性别,学分,课程名,课程号,成绩,E-R图,供应,库存,领导,工作,供应商,仓库,职工,零件,项目,供应量,库存量,项目,项目号,开工日期,预算,ER图转换成关系模式,1 : 1,系（系号，系名，电话，系主任）,系主任（工作证号，姓名，年龄）,ER图转换成关系模式,1 : N,学生（学号，姓名，性别，系号）,系（系号，系名，电话，系主任）,ER图转换成关系模式,M : N,学生（学号，姓名，性别，系号）,课程（课号，课名，学分）,选课（学号，课号，成绩）,成绩,实体集 关系 联系关系：由联系的各实体集主码属性， 以及联系本身的属性组成。,ER图转换成关系模式,职工(职工号，姓名，年龄，领导）,供应（供应商，项目，零件，数量）,Oracle 关系数据库概述,子程序调用接口OCI,Develop 2000,Pro*C 预编译接口,SQL*Plus,SQL*Forms、Menu,操 作 系 统,R D B M S O R A C L E 核 心,DB,：,终端,DB,DB,网络,虚 终 端,三级模式,Oracle 数据存储空间,基本表,Table,View,视图,SQL,Table,Table,表与视图,基本表 本身是实际且独立存在的。 是数据库存储数据的基本单元,视图 视图是虚表 数据库只存储视图的定义，不存储对应的数据。 视图用一组查询来定义，是被存储的查询,SQL语言基础,Select * From emp ;,六种类型的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为职工的上一级领导（即上一级领导的职工号）,CREATE TABLE student ( sno NUMBER(6) , sname CHAR(6) , sex CHAR(2) , birthday DATE , dno CHAR(3) ) ;,建立表对象,列数据类型,字符型： CHAR（n）字符数据，长度为n个字符 VARCHAR2（n）变长字符型(n为最大长度) 数值型： NUMBER（n）数值数据。最大可达105位 NUMBER（m， n）m位数字，其中n为小数位 日期型： DATE ，其格式为DDMONYY 例如：17DEC85 或1712月85,模式,Create or replace 模式名.表名 模式逻辑地组织数据库对象 不同模式中的对象可以有相同的名字 在Oracle系统中，模式名与用户名是一一对应的 scott用户拥有emp表 scott模式含有emp表 sys模式中含有数据字典（系统表/系统目录）,完整性约束,(1)主码完整性约束 sno number(6) constraint e1 primary key (2) 非空完整性约束 sname char(6) constraint e2 not null (3) 唯一完整性约束 dname char(20) constraint d2 unique,完整性约束,(4) check完整性约束 age number constraint e3 check(age between 18 and 60) sex char(2) check(sex in(男,女) (5) 引用完整性约束 dno char(3) constraint e4 references dep(dno) on delete cascade,引用完整性约束,先建主表，后建子表 子表引用的必须是主表的主码（或定义了unique约束的列）。 子表dno的值必须是合法值(主表值) 当删除主表记录时，若子表中有对应记录，不能删。 指定了on delete cascade 连带删除。,CREATE TABLE sc (sno number(6), cno char(3), grade number(3), primary key (sno,cno) ) ;,CREATE TABLE ( not null(列1，列2）， unique(列1，列2） ) ;,表约束,CREATE TABLE student (, foreign key(dno) references dep(dno) );,CREATE TABLE emp ( eno char(3), ename char(6), sex char(2), age number(2), dno char(3), CHECK(sex=男AND age BETWEEN 18 AND 60 OR sex=女AND age BETWEEN 18 AND 55 ) )；,表约束,：,完整性约束的作用时间,create table student ( ) enable constraint e1 enable constraint e2 ; disable,表对象的修改与删除,ALTER TABLE emp ADD（列定义） MODIFY（ename char(10)） ADD（表约束） DROP CONSTRAINT e4 ； DROP TABLE emp ；,DML 语句(DB写语句）,INSERT INTO emp VALUES(e01, 李宏，32 ,); UPDATE emp SET sal=sal*1.1 WHERE dno=d01 ； DELETE FROM emp WHERE dno=d01 ；,数据库查询,(1) 基本查询,SELECT * FROM student WHERE sex = 男 AND dno = D01 ; 查询的结果仍可看成是一个关系，它由查出的记录组成。,(2) 查询语句中的投影操作,投影操作是从关系的属性集中选择属性子集，即由关系的部分列组成一个新关系， SELECT sno , sname , dno FROM student;,(2) 查询语句中的投影操作,SELECT sno stu_no ，sname stu_ name ， dno dept FROM student WHERE sex男 ； 该语句输出如下： stu_no stu_name dept 980001 张自立 d01 970020 李春生 d02,(2) 查询语句中的投影操作,SELECT ename ，sal*12 an_sal FROM emp ; 该语句输出如下： ename an_sal 徐再兴 9600 SELECT ename, sal*12 an_sal , 元 RMB FROM emp ; 该语句输出如下： ename an_sal RMB 徐再兴 9600 元 ,(3) 查询语句中的选择操作WHERE子句,选择操作是从关系中选择部分满足条件的元组，其表示形式为： SELECT * FROM student WHERE dno=d01;,(3) 查询语句中的选择操作WHERE子句,例：查询79、80两年出生的女同学的名单： SELECT sname，birthday FROM student WHERE sex=女 AND birthday BETWEEN 01-1月-79 AND 31-12月-80； 查询1980年以前出生的学生： SELECT * FROM student WHERE birthday 01-1月-1980 ；,SQL语句中的运算符,算术运算符：，*，/ 逻辑运算符：与 AND、或 OR, 非 NOT 比较符：，!，=， 其他运算符：IN，NOT IN，ANY，ALL 判断是否是集合的成员。 BETWEEN AND 判断列值是否满足指定的区间 LIKE 匹配模式 _ % IS NOT NULL 测试空值,(3) 查询语句中的选择操作WHERE子句,在查询条件中，可以使用集合运算符（例如：IN） SELECT dname，tel FROM dep WHERE dname IN(计算机系，外语系，中文系) ； 该表达式等同于: dname=计算机系 OR dname=外语系 OR dname=中文系 该表达式也等同于: dname =ANY(计算机系,外语系,中文系)， 集合运算符NOT IN是IN的反义词， WHERE dname NOT IN(计算机系，外语系，中文系) ； 该表达式等同于: dname !=ALL(计算机系,外语系,中文系)，,(3) 查询语句中的选择操作WHERE子句,空值是表中分量常见的一种状态 查询学号为980001的同学正在学习的课程: SELECT cno FROM sc WHERE sno=980001 AND grade IS NULL； 模糊匹配。匹配符有两个： _ 表示任意一个字符 表示任意个字符（包括0个） 例如，查询姓王的同学信息； SELECT * FROM student WHERE sname LIKE 王 ；,(4)查询的排序输出 ORDER BY子句,查询c01课程的成绩，并按成绩由高到低的顺序输出： SELECT sno，grade (输出学号和成绩) FROM sc WHERE cno=c01and grade IS NOT NULL ORDER BY grade DESC ； 排序输出的隐含顺序是升序（ASC），如果要求按列值的降序输出，需在列名后指定DESC。 ORDER BY子句指定的排序列可以不止一个，例如： SELECT * FROM student ORDER BY dep DESC，birthday ；,(4)查询的排序输出 ORDER BY子句,ORDER BY 子句除了用列名指定排序列外，还可以依据SELECT 子句中列的顺序号排序，这对于某些列名不好指定的情况尤为有用。例如，查询职工姓名及年工资，并按年工资的升序依次输出： SELECT ename,sal*12 FROM emp ORDER BY 2 ； 排序应该是查询语句的最后一步工作，是即将输出之前的处理，所以排序子句一般也出现在查询语句的最后。,(5) 多表查询与连接操作（joins）,笛卡尔积操作将结果关系扩展，如果笛卡尔积的两关系元组数分别为m和n，那么结果关系的元组数为mn，而在处理中往往只是需要其中部分元组。 连接（Join）操作是从笛卡尔积中选择满足条件的元组。最常用的条件是等值连接,Select * from student,dep where student.dno=dep.dno,dno,student,dep,(5) 多表查询与连接操作（joins）,表之间的连接操作（通常是自然连接）体现在WHERE子句中的连接条件。 例：查询计算机系的学生信息 SELECT sno，sname，sex FROM student,dep WHERE student.dno=dep.dno AND dname=计算机系；,(5) 多表查询与连接操作（joins）,查询物理课不及格的学生名单（输出学生的学号、姓名及成绩）： SELECT student.sno,sname,grade FROM student,sc,course WHERE cname=物理 AND grade60 AND o=o AND sc.sno=student.sno ： 在该例中，查询的结果来自两张表，而决定查询结果的因素涉及到三张表的信息。,Select sname,dname from student,dep where student.dno(+) = dep.dno and dep.dno in(d01,d02,d03),Ename dname - - wang dep1 zhang dep1 li dep2 dep3,多表查询 外查询,显示目前没有学生的系： select dname from student , dep where student.dno(+) = dep.dno and sno is null,多表查询 外查询,(6) 元组变量,在一个查询过程中有时会涉及到同一关系的两个（或几个）元组。为了表示不同的元组，SQL允许在FROM子句中为一个关系定义几个元组变量。这样，我们就能在SELECT、WHERE等子句中，通过在属性前冠以元组变量来引用同一关系不同元组的属性值。,例如在emp表中，我们要查询职工王莹的上级领导信息 SELECT eno,ename FROM emp WHERE ename=王莹 AND mgr=eno ； ?,为emp表定义两个元组变量（e1,e2) SELECT e2.eno,e2.ename FROM emp e1,emp e2 WHERE e1.ename=王莹 AND e1.mgr=e2.eno ；,(6) 元组变量,athlete运动员（name姓名,sex性别,group组 ） 查询混双选手名单。每行输出一对选手，男在前，女在后。,SELECT man, woman FROM athlete a1,athlete a2 WHERE a1.sex=男 AND a2.sex=女 AND a1.group=a2.group ；,该例检索结果如下： man woman 王涛 邓亚萍 ,(6) 元组变量,交,差,并,(7) 集合运算,(7) 集合运算,select sno from sc where cno=c01 select sno from sc where cno=c02 ;,(交) union (并) minus (差),intersect,查同时选修了c01和c02两门课的学生 SELECT sno FROM sc where cno=c01 and cno=c02 ;,(7) 集合运算,例：查询选修了c01、但没有选修c02的学生的学号： （SELECT sno FROM sc WHERE cno=c01） MINUS （SELECT sno FROM sc WHERE cno=c02）；,(7) 集合运算,例：假如外语只开设了英语和日语。查询当前至少上着一门外语课的学生： (SELECT sno FROM sc,course WHERE cname=英语AND o=o AND grade IS null ) UNION (SELECT sno FROM sc,course WHERE cname=日语AND o=o AND grade IS null)；,(7) 集合运算,Select 语句必须选择同样数量的列。 相应的列必须具有相同的类型。 重复的行将被自动去掉，不用distinct 第一个select 语句中的列名出现在结果 Order by 子句可出现在语句的最后，且只能指定位置列。,(Select empno,empname,salary from worker ) union (select eno,ename,sal from emp ) order by 3 ;,(8)查询中的重值处理,我们的讨论中存在这样一个事实，即SQL把关系当作一个“袋子”而不是一个集合。也就是说，一个元组在关系中可以出现若干次。 例如，我们要查询一下所有的课程名： SELECT cname FROM course； 只需在关键字SELECT 的后面加上关键字DISTINCT即可。它告诉SQL在结果关系中只产生元组的一份拷贝，因此可以保证结果中没有重复值。 例如，上面提到的查询可以写成这样： SELECT DISTINCT cname FROM course ；,(8)查询中的重值处理,在前面讨论关系的集合运算,结果关系的重复值将会自动去掉。 但如果我们在集合运算符（如 UNION）的后面加上关键字ALL，那结果关系又会具有袋子的效果，即不去掉重复值。 例如，查询至少选修了c01和c02中一门课的学生: （SELECT sno FROM sc WHERE cno=c01） UNION ALL （SELECT sno FROM sc WHERE cno=c02）；,(8)查询中的重值处理,DISTINCT的使用是要付出代价的。因为要去掉重复值，必须对结果关系进行排序，相同的元组排在一起，只有按这种方法对元组分组，才能去掉重复值，而这一工作甚至比查询本身还费时间。,聚合是把关系作为运算对象的另一类操作。这里所说的聚合操作就是对关系中一列的值进行统计工作，最终得到一个统计值。例如求职工的平均年龄，求职工的工资总额等。 常用5种聚合的操作符： SUM 对某列的值求和。 AVG 求某列值的平均值。 MIN 求某列值的最小值。 MAX 求某列值的最大值。 COUNT 统计某列值的个数（计数）。,(9) 聚合操作,(9) 聚合操作,例：统计职工的工资总额： SELECT SUM（sal） FROM emp ； 例：求职工的最高工资、最低工资和平均工资： SELECT MAX(sal)，MIN(sal)，AVG(sal) FROM emp ； 例：求女学生的学生总数： SELECT COUNT(*) FROM student WHERE sex=女；,(9) 聚合操作,查询有多少名同学物理课不及格： SELECT COUNT(*) FROM sc,course WHERE cname=物理 AND o=o AND grade60；,将关系的元组按一定条件分组，然后对每一个组进行统计。 求每个系的学生数而不是求学生总数。希望得到下面的输出： dno count(*) d01 120 d02 65 SELECT dno,count(*) FROM student GROUP BY dno ；,d01,d01,d01,d01,d01,d01,23,d01,45,100,76,221,65,90,sum,max,dno,count,(9) 聚合操作-分组统计,(9) 聚合操作 -分组统计,查询每个学生已获得的学分（注：成绩及格即取得相应课程的学分）： SELECT sno,sum(credit) FROM sc,course WHERE grade=60 AND o=o GROUP BY sno ；,分组子句中可以指定多个用于分组的属性。当按第一个属性值分组后，每组再按第二个属性值分组，统计是对最小的组进行。 查询各系c01课程最高成绩和最低成绩（男生、女生分开统计）： SELECT dno,sex,MAX(grade),MIN(grade) FROM student,sc WHERE student.sno=sc.sno AND cnoC01 GROUP BY dno,sex ；,dno sex MAX(grade) MIN(grade) d01 男 100 45 d01 女 98 61 d02 男 94 60 d02 女 90 57 ,(9) 聚合操作分组统计,(9) 基于聚合操作结果的选择,当我们的选择是基于组的统计结果时，要在与GROUP BY 子句配合使用的HAVING子句中写上这类选择组的条件。 例如：查学分大于30分的学生（输出学号及学分） SELECT sno,sum(credit) FROM sc,course WHERE grade60 AND o=o GROUP BY sno HAVING sum(credit)30；,(10) 子查询,所谓子查询就是一个用括号括起的表达式，它对关系求值。 例如:一个SELECTFROMWHERE表达式就可以是一个子查询。 注意： 子查询的嵌套 如何处理子查询的返回值 相关子查询和非相关子查询,一个子查询可以产生一个单纯数据，如果是这种情况，该子查询就如同一个常量、或是一个表示元组分量的属性，那么我们就可以象使用常量或属性一样来使用子查询。,例如，查询物理系的学生名单，按以前的做法是： SELECT sname FROM student,dep WHERE dname=物理系 AND dep.dno=student.dno ；,而利用子查询的语句如下： SELECT sname FROM student WHERE dno=(SELECT dno FROM dep WHERE dname=物理系) ；,(10) 子查询,(10) 子查询,一个子查询也可以产生一个关系，该关系可以包含若干属性，可包含若干元组。 SQL提供了若干对于关系的操作符，并产生一个布尔型的结果。这些操作符主要就用在子查询的结果上。 EXISTS 运算符 EXISTS R 是一个条件，当且仅当关系R不空时为真。 集合运算符 IN ，NOT IN ANY ！ALL ,(10) 子查询,例：查体育课不及格的男生名单： SELECT sname FROM student WHERE sex=男 AND sno IN ( SELECT sno FROM sc WHERE grade60 AND cno IN ( SELECT cno FROM course WHERE cname=体育 ) )；,查找年龄在50岁以上、工资小于本部门平均工资的职工，输出他们的姓名、年龄、工资： SELECT ename,age,sal FROM emp WHERE age50 and sal( SELECT avg(sal) FROM emp WHERE dno= )；,e1,?,dno,e1.dno,emp.dno,(10) 子查询 - 相关查询 - 属性域,例：查询从未被选修的课程： 相关子查询 SELECT cno,cname FROM course WHERE NOT EXISTS(SELECT * FROM sc WHERE cno=o)；,例：查询从未被选修的课程(另一种方法)： 非相关子查询 SELECT cno,cname FROM course WHERE cno NOT IN(SELECT cno FROM sc )；,(10) 子查询,(10) 子查询与聚合操作,例：查询本学期选课超过10门的学生人数（注：选修但还没有成绩就表示是本学期正在上的课）： SELECT COUNT(*) FROM student WHERE sno IN (SELECT sno FROM sc WHERE grade IS NULL GROUP BY sno HAVING COUNT(*)10) ；,(10)子查询与聚合操作,例：查询各系年龄最小的同学名单： SELECT sname,birthday,dno FROM student WHERE (birthday,dno) IN ( SELECT MAX(birthday),dno FROM student GROUP BY dno ) ；,(11) 树查询,查询职工上下级关系 select eno,ename from emp connect by prior eno=mgr start with eno=e01,select level,eno,enamee,ENO ENAME - - e01 aaa e02 bbb e03 aaa e04 aaa e05 aaa e06 aaa e07 aaa e08 aaa,LEVEL ENO ENAME - - - 1 e01 aaa 2 e02 bbb 3 e03 aaa 3 e04 aaa 2 e05 aaa 3 e06 aaa 3 e07 aaa 3 e08 aaa,查同时选修c01和c02两门课的学生,select a.sno from sc a , sc b where a.sno=b.sno and o=c01 and o=c02 ;,Select sno from sc where cno=c01 and sno in (select sno from sc where cno=c02 ) ;,查询的不同实现,子查询的应用,create table emp1 as select eno,ename,sal,age,dno from emp ; insert into emp1 select * from emp where sex=男 ； 建视图： create view emp1 As select * from emp where dno=d01 order by age ;,修改视图,CREATE VIEW emp01 SELECT eno,ename,age,sal FROM emp WHERE dno=d01 ; 如果在视图emp01中插入该部门的一个新职工记录，插入语句如下： INSERT INTO emp01 VALUES(e01，李为，23，800)； ？ 可修改视图 CREATE VIEW emp01 SELECT eno,ename,age,sal,dno FROM emp WHERE dno=d01 ；,修改视图,删除： DELETE FROM emp01 WHERE age=60 ； 该语句被转换为如下等价语句 DELETE FROM emp WHERE age=60 AND dno=d01； 修改： UPDATE emp01 SET sal=sal*1.1 ； 该语句被转换为如下等价语句： UPDATE emp SET sal=sal*1.1 WHERE dno=d01；,SQL语句中的函数字符函数,例：select dno, lower(dname) from dep ; 例：select * from emp where ename=upper(rose);,SQL语句中的函数数值函数,例： select ename round(sal, 2) round (782.934 ,2) 782.93 round (782.934) 783 round(782.934 , -1) 780 log sin cos abs sqrt floor(99.9)=99 小于/等于参数值的最大整数,SQL语句中的函数日期函数,例：select sysdate from sys.dual ; dual(dummy) 日期的算术运算 date +number date - number date - date select d1,d1+7,d1-7,sysdate-d1 from emp; 04-jun-84 11-jun-84 28-may-84 1827.731,SQL语句中的函数日期函数,例： select months_between(sysdate,hiredate) from emp where months_between(sysdate,hiredate)24 ; 65.0872 26.8292 例：select add_month(hiredate,3) from 13-jun-98 13-sep-98 05-dec-97 05-mar-98,SQL语句中的函数日期函数,例：select next_day(sysdate,Monday) from dual 31-org-98 7-sep-98 例 ：select last_day(sysdate) from dual; 包含参数的月份中的最后一天,SQL语句中的函数转换函数,to_char(sysdate,描述串) 将隐含格式dd-mon-yy转换为指定格式 格式串： yy/mm/dd 98/06/11 hh:mi:ss 09:34:28 day Monday . Dy mon, tue wed . yyyy 1998 to_char(sal,$9,999) $1,375 select to_char(3456,$999,9) from dual,SQL*Plus,Sql*plus环境 报表功能,中文配置-regedit,SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280,主机字符串配置,主机字符串配置,主机字符串配置,主机字符串配置,主机字符串配置,主机字符串配置,主机字符串配置,主机字符串配置,登录 SQL*Plus,SQL*Plus窗口（交互界面）,Login.sql 初始化文件,SQL*Plus命令,SQL select * from cat ; SQL desc student SQLselect * from student ;,SQL*Plus命令,SQL edit a SQL a,SQL*Plus菜单使用,SQL*Plus菜单使用,替换变量,insert into emp values( enter value for eno: enter value for ename:,98/12/31,第一页,年度财务报表,内部资料，请勿外传,列标题设计： Column sal heading 工资 format $9999.99 报表头尾标题设计： COL n Ttitle SKIP n Btitle LEFT CENTER RIGHT 行分组： BREAK ON 列名 SKIP(n|page) ON BREAK ON dno SKIP 3 分组计算： compute sum of sal on dno (劈断列), XXXXXX ,ord